Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get a txt file to go to excel and have the columns work? | Excel Worksheet Functions | |||
Copying Columns of Text - WS Functions do not work correctly | Excel Worksheet Functions | |||
Convert Text to Columns with Excel 2007 beta does not work well | Excel Worksheet Functions | |||
Sumproduct doesn't work with columns... alternatives? | Excel Worksheet Functions | |||
Linking text columns with text and data columns | Excel Worksheet Functions |