Data,Text to Columns Clean up
 

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,

Data,Text to Columns Clean up
 

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,

Data,Text to Columns Clean up
 

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,

Data,Text to Columns Clean up
 

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,

Data,Text to Columns Clean up
 

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,

Data,Text to Columns Clean up
 

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,