Trim, Replace?

[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

 

[bj]

try
=substitute(current_function,"'","")

 

[John Bundy]

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)))

 

[Stephanie]

This will remove all:

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

 

[Saxman]

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

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!