Trim, Replace?
 

=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

Trim, Replace?
 

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

"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

Trim, Replace?
 

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

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"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

Trim, Replace?
 

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

Trim, Replace?
 

On 01/08/2007 21:46:04, Stephanie wrote:
=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!