up vote 5 down vote favorite
share [g+] share [fb]

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special the value of the result of the Trim function, it still has the whitespace on the end.

Could it be some other sort of whitespace character that Trim doesn't remove?

link|improve this question
100% accept rate
1  
If you look at it in the mode where you can see what kind of characters are there, what type of character is not being removed? – soandos Dec 22 '11 at 2:56
if you do =LEN(CELL) then what does it return? – kobaltz Dec 22 '11 at 3:43
1  
Out of curiosity, what is the ASCII number of the last character? Use =CODE(RIGHT(A1,1)) (assuming A1 contians the problematic text) to see it. Space is 32, Tab is 9, and new line characters are 10 and 13. – Hand-E-Food Dec 22 '11 at 4:05
1  
@Hand-E-Food, the result of that function is 160 – Highly Irregular Dec 22 '11 at 4:26
@kobaltz, =LEN(CELL) returns 9. The cell value is (without the double quotes) "010-0000 ", but trim doesn't seem to remove the last character. The copy and paste to put the value here may well be modified to be a space by my browser, or some other means... – Highly Irregular Dec 22 '11 at 4:29
show 1 more comment
feedback

3 Answers

up vote 6 down vote accepted

Character 160 is a non-breaking space, a character designed to look like a space but still be part of the word, forcing the adjacent words to stay together. You can convert them to regular spaces and then use trim.

Assuming the text is in A1, use:

=TRIM(SUBSTITUTE(A1, CHAR(160), " "))
link|improve this answer
Thanks - works perfectly! – Highly Irregular Dec 22 '11 at 4:36
1  
+1. This link is a useful reference. Quite often CLEAN , TRIM and SUBSTITUTE are all needed for dealing with strings – brettdj Dec 22 '11 at 6:31
feedback
up vote 1 down vote

If it just has one space and let's assume that the column is A.

A1= "CELL A1 "
A2= "CELL A2 "

Then B1 would be =LEFT(A1, LEN(A1)-1)
Then B2 would be =LEFT(A2, LEN(A2)-1)
link|improve this answer
If that was what it was, then trim would work... – soandos Dec 22 '11 at 3:32
And from what the comments indicate, this would have worked just as well. – kobaltz Dec 22 '11 at 5:51
1  
Because you got lucky. The could have been more than one character there, and you never would have known. In addition, the other answer deals with it no matter where in the cell it occurs, making it a much better general answer in any case. – soandos Dec 22 '11 at 5:55
Unless it had something like ASCII 160 and ASCII 161. Where as, if you know the length of each string and you see that it's one off from what is expected, personally I do not see the issue with this. I'm open to try and understand your view, but both solutions do have their limitations in different environments. – kobaltz Dec 22 '11 at 6:04
Agreed. I guess I like the other answer more because he identified the problem, and then solved it. You have a solution to something that solves similar problems. I see your point though, +1. – soandos Dec 22 '11 at 6:20
show 2 more comments
feedback
up vote 0 down vote

I suggest it would be easier to select the entire worksheet and use Replace (Ctrl+H) to replace every non-break space (NBSP) with a space. This still leaves spaces at the end of cells but avoid removing NBSPs from the middle of cells. You still need Trim but that is easier than TRIM(SUBSTITUTE(...)).

I thought I would be able to enter a NBSP using Alt+160 but it did not work for me. I had to use Insert Symbol to place a NBSP into a cell, copy it out of there and drop it into the Find what field.

With Insert Symbol, NBSP is the empty cell on the third line under 4.

If this is a repeating problem, you could use the macro recorder to save the Replace. However, it might be better to write a macro which uses FIND to locate NBSP anywhere within any string and either replace it with space or delete it according to its position within the string. This macro would need to loop until FIND fails.

link|improve this answer
You can type NBSP with Alt+0160. One to three-digit numbers are ASCII, four-digit numbers are unicode. – Hand-E-Food Dec 22 '11 at 21:17
I gave up as soon as it didn't work. Your comment persuaded me to try again. I had forgotten it only worked with the numeric pad which on a laptop is in the middle of the keyboard. I will try to remember the next time I need to enter a character this way. Thanks for the prompt. – Tony Dallimore Dec 22 '11 at 21:46
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.