Splitting Text

[Guest]

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

 

[Pete_UK]

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

 

[Guest]

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

 

[T. Valko]

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.

 

[Guest]

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