Hi guys,
i have a question about removing extra space two words.
i copy raw data when i paste it, they always have space or spaces after words.
please see the file i have attached.
i tried =trim but it doesn't work maybe because it's 2 words?
thank you in advance!
use substitute to replace double spaces with single space
Please Login or Register to view this content.
I don't think its a space that's why TRIM isn't working.
It's a Unicode 160, where a space is Unicode 32Please Login or Register to view this content.
Ok thats weird, try this, Im copy blank spaces after 2 words and put them in:
=SUBSTITUTE(A1,"*","")
In B1,
=TRIM(SUBSTITUTE(A1, CHAR(160), " "))
Entia non sunt multiplicanda sine necessitate
hi Azumi,
just tried it and it seems like it will only work when there's only one space after words, do you have solution if there are more than 1 space after words?
thanks
Last edited by parkkibum; 04-04-2014 at 04:46 PM.
hi SHG, i just tried that formula, but when i paste the cell as value, it still leaves a blank after words
hi Foundy, i tried that formula but when i paste the cell as a value, it still leaves a space.
Originally Posted by foundy
I don't think its a space that's why TRIM isn't working.
It's a Unicode 160, where a space is Unicode 32Please Login or Register to view this content.
Sorry for late reply, please check my file
=TRIM(SUBSTITUTE(A1,"*"," "))
Thank you Azumi!!
it's perfect!
I didn't look at your file.
Instead of using a formula try this macro.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks