Text to Columns doesn't work
 

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?

Text to Columns doesn't work
 

Remove any hidden characters like CHAR(160)
--
Gary''s Student - gsnu200731


"ValerieJTO" wrote:

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?

Text to Columns doesn't work
 

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

"Gary''s Student" wrote:

Remove any hidden characters like CHAR(160)
--
Gary''s Student - gsnu200731


"ValerieJTO" wrote:

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?

Text to Columns doesn't work
 

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

ValerieJTO wrote:

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

"Gary''s Student" wrote:

Remove any hidden characters like CHAR(160)
--
Gary''s Student - gsnu200731


"ValerieJTO" wrote:

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?

--

Dave Peterson

Text to Columns doesn't work
 

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.

"ValerieJTO" wrote:

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

"Gary''s Student" wrote:

Remove any hidden characters like CHAR(160)
--
Gary''s Student - gsnu200731


"ValerieJTO" wrote:

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?

Text to Columns doesn't work
 

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!

"skibeaux" wrote:

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.

"ValerieJTO" wrote:

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

"Gary''s Student" wrote:

Remove any hidden characters like CHAR(160)
--
Gary''s Student - gsnu200731


"ValerieJTO" wrote:

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?