TRIM function and leading spaces

**nsv** · 09-25-2008, 10:24 AM

I have some cells containing imported text with a lot of leading spaces. I try to get rid of them by TRIM'ing those cells, but it doesn't work.
Is TRIM only for trailing spaces?

/NSV

**Richard Schollar** · 09-25-2008, 10:37 AM

No, trimming will work on leading spaces too. The fact that it isn't getting rid of your 'spaces' suggests to me they aren't actually spaces. Try Substitute:

=SUBSTITUTE(A1,CHAR(160),"")

CHAR(160) is the non-breaking space character, often confused with an actual space.

Richard

**martindwilson** · 09-25-2008, 11:36 AM

you could also try the =clean() function

**Andy Pope** · 09-25-2008, 11:44 AM

In my test CLEAN does not removes non-breaking space.

**martindwilson** · 09-25-2008, 12:56 PM

=clean(SUBSTITUTE(A1,CHAR(160),"")) should just about cover every thing then!
or find /replace alt+160 with nothing

**nsv** · 09-26-2008, 03:11 AM

The SUBSTITUTE works, thank you very much; I'm impressed that you could guess that the culprit was CHAR(160) :-)

/NSV

**nsv** · 09-26-2008, 04:24 AM

Well, it did work in my first trial, but second time it didn't.
I then tried CLEAN(SUBSTITUTE(A1,CHAR(160),"")), which also didn't work, but TRIM(SUBSTITUTE(A1,CHAR(160),"")) did the job.
I can't figure out what is happening, but I suppose I will - eventually. Anyway, thanks a lot for the tips about CLEAN and SUBSTITUTE; I wasn't aware of those functions.

/NSV

**Andy Pope** · 09-26-2008, 05:22 AM

Order of operation is important.

TRIM removes leading/trailing and double spaces, CHAR(32)
SUBSTITUTE remove non-breaking spaces, CHAR(160)
CLEAN would remove non-printable characters

**canll** · 10-01-2014, 07:40 PM

None of normal Trim formulas work in Win 8....excel 2007
Also f4 does not do normal absolute function

**protonLeah** · 10-01-2014, 09:33 PM

canll,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**FDibbins** · 10-01-2014, 10:32 PM

Agree completely with protonLeah, but just wanted to add that all trim functions as well as F4 do work perfectly fine in Win8 MS 2007...that is the config I am running right now