Text to Columns

[SarahJ]

I have First and last names that were downloaded from a website. The names
appear to be separated by a space, but low and behold when I tried to use
text to columns to separate them it did not work. I had to go and change
what ever is separating the name to a space on one of the records to get it
to work. Any ideas on how to accomplish this and what might possibly be
separating the names.

 

[RagDyeR]

It's probably Char(160), a non-breaking space which is used very often in
web sites.

Try TTC again, but click on "other" for delimiters,
THEN, in the window, hold down <Alt>,
and key in
0160
using the numbers from the num keypad, *not* the numbers under the function
keys.

Of course, the box will appear empty, since it *is* a <space>.

The "Data Preview" window should display the separation ... if my guess was
correct.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have First and last names that were downloaded from a website. The names
appear to be separated by a space, but low and behold when I tried to use
text to columns to separate them it did not work. I had to go and change
what ever is separating the name to a space on one of the records to get it
to work. Any ideas on how to accomplish this and what might possibly be
separating the names.

 

[Bob Phillips]

Try these formulae

=LEFT(A1,FIND(CHAR(160),A1&CHAR(160))-1)

=SUBSTITUTE(A1&CHAR(160),B1&CHAR(160),"")

 

[Jan Karel Pieterse]

Hi SarahJ,

Any ideas on how to accomplish this and what might possibly be
separating the names.

Select the seperating char and hit Control+c
Then do a search and replace, control+v into the search box and space into the
replace box. Then replace all.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[SarahJ]

THANKS!!!!!!!!!!!!!!!!!!!!!!! it was Char(160) and the Alt 0160 Worked
perfectly!!!! YIPPIE!!!!!!!!!!!!

 

[Ragdyer]

You're welcome, and appreciate your very enthusiastic feed-back.<g>