Posted to microsoft.public.excel.worksheet.functions
|
|
trailing spaces
Thank you both very much.
peter
"Rick Rothstein (MVP - VB)" wrote:
If you can't use the macro that Gary's Student suggested, then consider this
formula which will trim only the outside spaces...
=LEFT(SUBSTITUTE(MID(A1,FIND(LEFT(TRIM(A1),1),A1), 1000),RIGHT(TRIM(A1)),RIGHT(TRIM(A1))&"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),""))),FIND("|",S UBSTITUTE(MID(A1,FIND(LEFT(TRIM(A1),1),A1),1000),R IGHT(TRIM(A1)),RIGHT(TRIM(A1))&"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),""))))-1)
Rick
"peter" wrote in message
...
Hi,
Found these formulas on the web
=LEFT(A803,MATCH(2,1/(MID(A803,ROW(INDEX($1:$65536,1,1):INDEX($1:$65536 ,LEN(A803),1)),1)<"
"))) (an array) deletes trailing spaces
=REPLACE(A804,1,FIND(LEFT(TRIM(A804),1),A804)-1,"")
deletes beginning spaces
Is it possible to combine these into one formula to remove both beginning
and
trailing spaces? I can't use trim because it will also remove duplicate
inbeded
spaces.
thanks,
peter
|