I s'pose you could try:
=UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9, "("},A2&"0123456789("))-1)))

which will parse the string at either the first number or first "(" it
finds. Just be sure the rest of your data does not have something in
parentheses "(" that you want to be included in the results. Otherwise, more
details would be needed concerning exactly what conditions would have to be
met to chop off the text inside the parentheses.




"Saxman" wrote:

I posted recently under the 'Parsing' heading.

The following function works fine except when it encounters certain data.

=UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9} ,A2&"0123456789"))-1)))

This converts:-

Maggie May 33 to MAGGIE MAY
Have Your Own Way 691 31J) to HAVE YOUR OWN WAY,
but My Immortal (ex6) 32 gets converted to,
MY IMMORTAL (EX.

Altering the value at the end of the function to -4 gets rid of '(EX', but
also trims Maggie May 33 to MAGGIE M, which defeats the object for the
VLOOKUP function, i.e, you gain some and lose some.

Any way around this?