Splitting Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Calder - 26 Jul 2007 01:12 GMT

Hi

I have W2K with Excel2K

I have a worksheet that links to another file.

In cell A2 the link returns the following value: PAINT-CLEAR EPOXY
PRIME-EE00155-A
In cell B2 the link returns the following value: PAINT-STOCKADE-FN22615-T

I have a helper column (H2 & H3) that allows me to remove the PAINT- from
the above values

H2 returns the value CLEAR EPOXY PRIME-EE00155-A (using =MID(A2,7,40)
H3 returns the value STOCKADE-FN22615-T (using =MID(A3,7,40)

Now what I would like to do 2 more things.

Remove the hyphen from the text from the above values and have the Name in
one column and the associated serial number in another. Is this possible? If
the number of letters were the same in each instance I am sure it would be
quite simple but as the names vary in length I have no idea on how to
accomodate this?

John

Reply to this Message

Pete_UK - 26 Jul 2007 01:22 GMT

You can use SEARCH or FIND to look for the position of the (first)
hyphen, and use that to extract the LEFT part in one cell and the
RIGHT part in another cell - you'll also need to make use of the LEN
function (length of the string).

Hope this helps.

Pete

On Jul 26, 1:12 am, John Calder <JohnCal...@discussions.microsoft.com>
wrote:

> Hi
>
[quoted text clipped - 21 lines]
>
> John

Reply to this Message

John Calder - 26 Jul 2007 03:06 GMT

Thanks Pete for your prompt response

I only gave a 2 line example, but in actual fact I havew about 1,000 rows of
data that need to be adjusted and many of them have varying lengths of text
so I was hoping the was a formula I could use in a couple of helper columns
to achieve this.

John

> You can use SEARCH or FIND to look for the position of the (first)
> hyphen, and use that to extract the LEFT part in one cell and the
[quoted text clipped - 32 lines]
> >
> > John

Reply to this Message

T. Valko - 26 Jul 2007 07:23 GMT

To extract the names from:

H2 = CLEAR EPOXY PRIME-EE00155-A
H3 = STOCKADE-FN22615-T

Enter this formula in I2:

=LEFT(H2,FIND("-",H2)-1)

To extract the serial numbers:

Enter this formula in J2:

=SUBSTITUTE(MID(H2,FIND("-",H2)+1,255),"-","")

Select both I2 and J2 and copy down as needed.

Signature

Biff
Microsoft Excel MVP

> Thanks Pete for your prompt response
>
[quoted text clipped - 49 lines]
>> >
>> > John

Reply to this Message

John Calder - 27 Jul 2007 04:00 GMT

Biff

Your formulas worked great !

Only thing is that they are used in helper colums for a pivot table.

The data is in columns A through to J and is 500 rows deep

Columns A to E are links to another spreadsheet and column F to J are
formulas used in the helper columns.

The problem I am now having is that when I try to create the pivot table
using this data I get the following error message

******************************************************************
"Microsoft Excel cannot make this change because there are too many row or
column items. Drag at least one row or column field off the pivot table, or
to the page position. Alternatively, right click a field, and then click Hide
or Hide Levels on the shortcut menu."
******************************************************************

The pivot table has only one entry in the Page area, five entries in the row
area and one entry in the data area.

Could you tell me if the pivot table cannot accept more than four entries in
the row area?

Thanks for your help

John

> To extract the names from:
>
[quoted text clipped - 66 lines]
> >> >
> >> > John

Reply to this Message

Rate this thread: