Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
When using this feature on a single cell to separate first and last names, it
works perfectly, but when using it on a range of cells in a single column, it clears the data from the third column. Ex: Cell A3 has a first and last name. I insert a column, so cell B3 is empty. I split the data using a space as the delimiter and A3 has the first name, B3 has the last name. I select the rest of the column, go through the same motions, the same thing happens, but column C has now lost all data. I'm working around this by inserting an extra blank column, but when I have to do this in multiple worksheets it gets very annoying. I'm using Excel 2007. Thanks for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
That's the way it is designed, it warns that it will overwrite adjacent
columns if they are not empty and yes the workaround is to insert blank columns -- Regards, Peo Sjoblom "Michael" wrote in message ... When using this feature on a single cell to separate first and last names, it works perfectly, but when using it on a range of cells in a single column, it clears the data from the third column. Ex: Cell A3 has a first and last name. I insert a column, so cell B3 is empty. I split the data using a space as the delimiter and A3 has the first name, B3 has the last name. I select the rest of the column, go through the same motions, the same thing happens, but column C has now lost all data. I'm working around this by inserting an extra blank column, but when I have to do this in multiple worksheets it gets very annoying. I'm using Excel 2007. Thanks for any help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
I'll try again. My question was too wordy and not clear.
I have already inserted an empty column for the last names. Single cell text to columns: First name in A1, Last name in B1, C1 left alone. Range text to columns: First name in A1:A347, Last name in B1:B347, C1:C347 data is cleared. I get the same warning both times, and when applying this to a range, I have to insert a blank column C, then delete it. Thanks. "Peo Sjoblom" wrote: That's the way it is designed, it warns that it will overwrite adjacent columns if they are not empty and yes the workaround is to insert blank columns -- Regards, Peo Sjoblom "Michael" wrote in message ... When using this feature on a single cell to separate first and last names, it works perfectly, but when using it on a range of cells in a single column, it clears the data from the third column. Ex: Cell A3 has a first and last name. I insert a column, so cell B3 is empty. I split the data using a space as the delimiter and A3 has the first name, B3 has the last name. I select the rest of the column, go through the same motions, the same thing happens, but column C has now lost all data. I'm working around this by inserting an extra blank column, but when I have to do this in multiple worksheets it gets very annoying. I'm using Excel 2007. Thanks for any help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
Most likely you have trailing spaces or something (could be invisible html
characters if you get this info from the web) and the last invisible character goes to column C Insert a column D, then in the first adjacent cell put =LEN(C1) copy down if you don't get zero it is not empty if you get 1 you can test what it is =CODE(C1) 160 is web characters space should return 32 Anyway, you can remove the last column in text to columns step 3, click the header in the preview box and select "do not import column (skip)" under column data format -- Regards, Peo Sjoblom "Michael" wrote in message ... I'll try again. My question was too wordy and not clear. I have already inserted an empty column for the last names. Single cell text to columns: First name in A1, Last name in B1, C1 left alone. Range text to columns: First name in A1:A347, Last name in B1:B347, C1:C347 data is cleared. I get the same warning both times, and when applying this to a range, I have to insert a blank column C, then delete it. Thanks. "Peo Sjoblom" wrote: That's the way it is designed, it warns that it will overwrite adjacent columns if they are not empty and yes the workaround is to insert blank columns -- Regards, Peo Sjoblom "Michael" wrote in message ... When using this feature on a single cell to separate first and last names, it works perfectly, but when using it on a range of cells in a single column, it clears the data from the third column. Ex: Cell A3 has a first and last name. I insert a column, so cell B3 is empty. I split the data using a space as the delimiter and A3 has the first name, B3 has the last name. I select the rest of the column, go through the same motions, the same thing happens, but column C has now lost all data. I'm working around this by inserting an extra blank column, but when I have to do this in multiple worksheets it gets very annoying. I'm using Excel 2007. Thanks for any help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
Thanks for your help. I found some miscellaneous characters that were
causing problems. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Excel is automatically doing "text to columns" upon paste text. | Excel Discussion (Misc queries) | |||
Linking text columns with text and data columns | Excel Worksheet Functions |