Text to Columns doesn't work

[Guest]

I have first and last names in a column that I copied and pasted from the
internet. I did some of my own data cleaning (remove hyperlinks, remove
titles, etc), and now I just want to separate into two columns. For some
reason, Text to Columns (Delimited with Space) is not working. I checked the
formatting of cells, and they appear to be normal - I tried with both General
and Text formats. Any ideas on how to fix?

 

[Guest]

Remove any hidden characters like CHAR(160)

 

[Guest]

I don't understand - could you be more specific?

Remove any hidden characters like CHAR(160)

 

[Dave Peterson]

I think that Gary''s Student guessed that you did a data|text to column and
specified that your data was delimited by a space.

But sometimes the data copied|Pasted from a web page have those HTML
non-breaking spaces in them. They look like spaces, but aren't.

I'd try to clean them up before doing more.

If you want to try...

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

The spaces may not be true spaces, therefore text to columns is seeing one
continuos line of text. You would need to delete the apparent space and
insert a new space or perhaps just insert commas then delimit by comma.

 

[Guest]

Thanks! I highlighted and copied the "apparent space" and put that in the
Find field of the Find/Replace Dialog box. Then I put a real space in the
Replace field of same dialog box. I hit replace all, and then Text to
Columns worked like it should have. Thanks for the tips!