Text to columns question

[Heidi]

I am using a program that will save a report as Excel. In this report I have
one column with First and Last name. I try text to columns to separate but
it won't work. I have to physically put a space in between the first and
last name to achieve this. Is there a way to do this without having to
manually put in the space?

 

[Mike H]

Hi,

Text to colums requires a delimeter or for your string to be split 'fixed
width' and it seems unlikely all your first names will be of the same length.
Post some sample data and someone may be able to help.

Mike

 

[Heidi]

Keith Atkinson
Jessica Bertrum
Gary Blenker
Pete Brang
Jeff Davies
Perry Ellering
Shannon Ellering
Kristen Finken
AIMEE GERADS
JON HINNENKAMP
KENNY HINNENKAMP
Jeff Houston
Ryan Imdieke

This is an example of the column of names. Excel is not recognizing the
space that is already between the two names, and when I add a space to conert
to two columns, then I have to physically remove the extra space.

 

[Rick Rothstein]

What is between the First and Last names now? If they are separated with
something that is not a space, but it looks like a space, then you can do
this to separate them. Select any one cell with the mysterious "space" in it
and highlight (select) this character, then press Ctrl+C to copy it into the
Clipboard. Now select all of your First/Last names; then go into the Text To
Columns dialog box and choose delimited in Step 1; on Step 2, put your
cursor in the blank box next to the "Other" checkbox and press Ctrl+V to
paste the contents of the Clipboard (which contains the mysterious space
character) into it (this will automatically select the "Other" checkbox).
Note... the names should now show with a separator between them in the table
area of the dialog box. Click the Finish button and your names should all be
separated.

 

[Mike H]

Heidi,

Lets suppose yo have Keith Atkinson in A1, what does the formula below
return?

=CODE(MID(A1,6,1))

Mike

 

[Heidi]

Thank you very much!!! What a time saver!!

What is between the First and Last names now? If they are separated with
something that is not a space, but it looks like a space, then you can do
this to separate them. Select any one cell with the mysterious "space" in it
and highlight (select) this character, then press Ctrl+C to copy it into the
Clipboard. Now select all of your First/Last names; then go into the Text To
Columns dialog box and choose delimited in Step 1; on Step 2, put your
cursor in the blank box next to the "Other" checkbox and press Ctrl+V to
paste the contents of the Clipboard (which contains the mysterious space
character) into it (this will automatically select the "Other" checkbox).
Note... the names should now show with a separator between them in the table
area of the dialog box. Click the Finish button and your names should all be
separated.