Often when I do a text to columns
the text has trailing spaces

like

xyz____ Viewable at xyz but len(a1) = 7

<<< the underscore characters represents trailing spaces not the literal
underscore as shown)

Using a regular trim(A1) doesn't work.
What other ops are available?
TIA,

JMay,
In step2 where you mark the delimiters check the "Other" check box and Type
Alt+0160
from the number key pad. (key in 0160 while keeping the "Alt" key down)
Cecil


"JMay" wrote in message
news:zvCvc.16758$Tw.8177@lakeread06...
Often when I do a text to columns
the text has trailing spaces

like

xyz____ Viewable at xyz but len(a1) = 7

<<< the underscore characters represents trailing spaces not the
literal
underscore as shown)

Using a regular trim(A1) doesn't work.
What other ops are available?
TIA,

Thanks,
works great!!

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
JMay,
In step2 where you mark the delimiters check the "Other" check box and
Type
Alt+0160
from the number key pad. (key in 0160 while keeping the "Alt" key down)
Cecil


"JMay" wrote in message
news:zvCvc.16758$Tw.8177@lakeread06...
Often when I do a text to columns
the text has trailing spaces

like

xyz____ Viewable at xyz but len(a1) = 7

<<< the underscore characters represents trailing spaces not the
literal
underscore as shown)

Using a regular trim(A1) doesn't work.
What other ops are available?
TIA,

2 q's:
I guess a check the "other box" in addition to (along with) the space
option box (already or previously checked).
Obviously something is happening "under the hood" as you do the Alt-0160 <<
though nothing visually is happening. right?
Thanks again.
JMay

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
JMay,
In step2 where you mark the delimiters check the "Other" check box and
Type
Alt+0160
from the number key pad. (key in 0160 while keeping the "Alt" key down)
Cecil


"JMay" wrote in message
news:zvCvc.16758$Tw.8177@lakeread06...
Often when I do a text to columns
the text has trailing spaces

like

xyz____ Viewable at xyz but len(a1) = 7

<<< the underscore characters represents trailing spaces not the
literal
underscore as shown)

Using a regular trim(A1) doesn't work.
What other ops are available?
TIA,

JMay,
It is a character called "No Brake Space" which is in 160 of the extended
keyboard.
you can get it in to a cell by using the formula =char(160) or doing the
Alt-0160.
at the worksheet level you can do find and replace, Find What Alt-0160
replace with nothing.
lot of those come in when you do copy/paste from web pages.
regards,
Cecil

"JMay" wrote in message
news:grEvc.17835$Tw.4571@lakeread06...
2 q's:
I guess a check the "other box" in addition to (along with) the space
option box (already or previously checked).
Obviously something is happening "under the hood" as you do the Alt-0160
<<
though nothing visually is happening. right?
Thanks again.
JMay

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
JMay,
In step2 where you mark the delimiters check the "Other" check box and
Type
Alt+0160
from the number key pad. (key in 0160 while keeping the "Alt" key down)
Cecil


"JMay" wrote in message
news:zvCvc.16758$Tw.8177@lakeread06...
Often when I do a text to columns
the text has trailing spaces

like

xyz____ Viewable at xyz but len(a1) = 7

<<< the underscore characters represents trailing spaces not the
literal
underscore as shown)

Using a regular trim(A1) doesn't work.
What other ops are available?
TIA,

Hi,

Adding to Cecilkumara Fernando's excellent suggestion, you may well like to
look at David McRitchie's TrimAll macro:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Read also David's notes.

---
Regards,
Norman

"JMay" wrote in message
news:zvCvc.16758$Tw.8177@lakeread06...
Often when I do a text to columns
the text has trailing spaces

like

xyz____ Viewable at xyz but len(a1) = 7

<<< the underscore characters represents trailing spaces not the
literal
underscore as shown)

Using a regular trim(A1) doesn't work.
What other ops are available?
TIA,