Hi..
I have used following two types of formula to remove the hidden unknown character / Extra space from the name. But extra space and special character is not removed. Sample sheet has been attached. can you help me to share the excel format to fix this.
=SUBSTITUTE(A4,CHAR(160)," ")
=TRIM(A4)
Thanks.. Senthil
I just clicked in the first cell (A4), highlighted the space you want removed then copied it, opened find and replace, pasted it into find and left replace blank, then hit replace all and it is gone.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
You can combine both formulae like this:
=TRIM(SUBSTITUTE(A4,CHAR(160),""))
Note that I have removed the space between the "".
Hope this helps.
Pete
No formulas needed. You can "clean" that data right in place.
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. the formula works well.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks