trailing spaces
 

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

trailing spaces
 

Take advantage of the fact that in VBA TRIM dows not touch internal spaces at
all. Use this tiny UDF:

Function NotSoTrim(r As Range) As String
NotSoTrim = Trim(r.Value)
End Function
--
Gary''s Student - gsnu200750


"peter" wrote:

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

trailing spaces
 

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

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