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

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

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

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