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" wrote:


=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