Split text into 2 separate columns

  • Thread starter Thread starter Nate
  • Start date Start date
N

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
 
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
 
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
 
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
 
In the next cell across, put =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))).
Click to expand...

This could be further shortened, with some rearranging, to

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

tim
 
Back
Top