trailing spaces

P

peter

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
 
G

Gary''s Student

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
P

peter

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top