Trim, Replace?

  • Thread starter Thread starter Saxman
  • Start date Start date
S

Saxman

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

With the grateful help of previous posters, the above function removes all
the following data from the example below:-

Big's Boy (ex7) 78J
becomes
BIG'S BOY

Is there a function that will remove the apostrophe to become
BIGS BOY

Name can consist of several words which I wish to keep intact for a VLOOKUP
function.

TIA
 
Try this mess out
=LEFT(A1,FIND("'",A1)-1) & RIGHT(A1,LEN(A1)-FIND("'",A1))
only gets the apostophe
this handles errors as well in needed
=IF(ISERROR(LEFT(A1,FIND("'",A1)-1) &
RIGHT(A1,LEN(A1)-FIND("'",A1))),A1,LEFT(A1,FIND("'",A1)-1) &
RIGHT(A1,LEN(A1)-FIND("'",A1)))
 
This will remove all:

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

This works fine! I was thinking of having a 'helper' column to gid rid of the
apostrophies and then cleaning up, as in a previous post, but this is much
tidier.

Thanks a million!
 
Back
Top