I have a column of text, some having leading spaces. I must remove all leading spaces.
I have tried =trim(cell), but it does not remove them. I then tried
=trim(clean(cell)) thinking that there might be some unprintables lurking.
That also does not work. I can manually remove the leading spaces, so I can see them [or the lack of them], but Excel does not seem to be able to work as needed.
Am I doing something wrong? It seems Trim should work.
I would MOST appreciate any thoughts or solutions.
Try copying one of the characters, then deselecting the cell and doing a Replace All, pasting the character you just copied. Possibly CHAR(160)
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
If it is CHAR(160), give this a try
Please Login or Register to view this content.
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
TMS, I don't think I understood your suggestion correctly. I am far from an Excel expert. But, gmr, your suggestion worked! That strips what I suppose is char(160) away, leaving exactly what I need
THANK YOU BOTH!
TMS, I don't think I understood your suggestion correctly. I am far from an Excel expert. But, gmr, your suggestion worked! That strips what I suppose is char(160) away, leaving exactly what I need
THANK YOU BOTH!
(how do I mark this as solved?)
You're welcome
To mark as solved:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
TMS deserves some credit for this as well. Had he not mentioned CHAR(160) in his post, it wouldn't have clicked with me what the problem was.
@gmr4evr1: thanks. inventorgeorge has already +repped me, although I suspect that your solution made his progress quicker. All good
If you need to do this frequently the macro at this website
will remove all leading/trailing and multiple interspersed
char 32 space characters. It will also remove and/or convert
char 160 non breaking spaces into standard char 32 space
characters. It will work on text or numbers and the numbers will be
converted to true numeric numbers.
I use this macro dozens of times every single day!
It's a real time saver.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for the info Tony.
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks