Issues getting TRIM to work

[physical_harry]

Hi all, I've only had a quick search around (so apoloogies if already covered, I'm trying to get something done very fast!), but I'm just trying to clear the trailing/leading spaces in a database. At first tried find & replace with '[:space:]+ but it only cleared trailing spaces (and I have quite a few leading ie before text).

I then tried TRIM, which appeared to be working, and then when I went to try it on my original sheet it stopped working! What I tried is '=TRIM(A1)' then dragged down to apply to the whole column, and across for the whole sheet.

So does anyone have any idea what I'm doing wrong, is it something which is even possible?!

Thanks,

Harry

[ken johnson]

One possibility is the leading space is not a space (ASCII 32).
It could be a non-breaking space (ASCII 160).
Test using the CODE function which returns the ASCII code for the first character.
If =CODE(A1) returns 160 the first character in A1 is a non-breaking space that is not removed by the TRIM function (or the CLEAN function).

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

[physical_harry]

Ah yes I just checked, it is 160 as you thought. So is there any solutions to remove these spaces other than manually (I really hope so...!)

Thanks for your help Ken

[ozzie]

=SUBSTITUTE(A1;CHAR(160);"")
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

[ken johnson]

You can also use Find & Replace.
Copy one of the non-breaking spaces into the "Search for" textbox and leave the "Replace with" textbox empty.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).