Text to Columns

[Guest]

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.

 

[Peo Sjoblom]

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

 

[Guest]

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]

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

 

[Guest]

Thanks for your help. I found some miscellaneous characters that were
causing problems.