Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using =TRIM() | Excel Worksheet Functions | |||
Trim | Excel Worksheet Functions | |||
Trim | Excel Discussion (Misc queries) | |||
Trim help please | New Users to Excel | |||
trim | New Users to Excel |