Split text into 2 separate columns

[Nate]

I have a column that is full of names (First-name Last-
name). Only a space separates each name. I'm curious if
I can split the names into two columns, first name in
column 1, and last name in column 2. I'm not sure it can
be done because of the different numbers of characters in
each name. I would think the only thing that can define
the split would be the space between each name. Does
anyone know if Excel can do this? and if so how? Thanks!

Nate

 

[Peo Sjoblom]

Use data>text to columns and space as delimiter.
With some names that would probably cause some problems
since extensions like JT. and III plus double names but
most names should be ok, the rest could be taken care
of manually

 

[Dan E]

Nate,

Select column A, Make sure B is empty,
Go "Data" -> "Text to Columns"
Choose "Delimited" and go next
Select "Space" as your delimiter and go next
Click Finish

Dan E

 

[Mike Boardman]

Nate

It's actually quite easy, and there are probably more ways to do it than the
following:

1. Highlight the entries, ensuring beforehand that you have a couple of
completely blank columns to the right. Click Data> Text to columns and
follow the Wizard through. You'll need to ensure that the separator is set
to "space".

2. Use a formula. Again, make sure you have a couple of empty columns to
the right. Then, (assuming your first entry is in cell A1) put the following
alongside the first entry: =LEFT(A4,FIND(" ",A1)-1).

In the next cell across, put =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))).

Copy both down as far as necessary, and that should be it.

This assumes that the parameters are exactly what you said: Only two names,
all separated by a space.

HTH
Mike B

 

[Tim Otero]

In the next cell across, put =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"

","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))).

This could be further shortened, with some rearranging, to

=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))

tim