trailing spaces

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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("|",SUBSTITUTE(MID(A1,FIND(LEFT(TRIM(A1),1),A1),1000),RIGHT(TRIM(A1)),RIGHT(TRIM(A1))&"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),""))))-1)

Rick
 
Thank you both very much.

peter


Rick Rothstein (MVP - VB) said:
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("|",SUBSTITUTE(MID(A1,FIND(LEFT(TRIM(A1),1),A1),1000),RIGHT(TRIM(A1)),RIGHT(TRIM(A1))&"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),""))))-1)

Rick
Click to expand...
 
Back
Top