Software

Use Excel's Text To Columns command to separate first and last names

If you need to split a list of names into two columns, you don't have to do it manually. See how this Excel wizard can quickly knock out the task for you.

Note: This article is also available as a PDF download.

When importing data into a worksheet, it is often necessary to separate the cell contents into two columns. For example, say you've imported the list of names shown in Figure A.

Figure A

For your mailing program, you need to separate the first and last names into two columns. Follow these steps:

  1. Select A2:A10.
  2. Go to Data | Text To Columns. In Excel 2007, click the Data tab and then click the Text To Columns command in the Data Tools group.
  3. In Step 1 of the Convert Text To Columns Wizard, click Delimited (Figure B).

Figure B

  1. Click Next to advance to Step 2 of the wizard, then select the Space check box and clear the Tab check box in the Delimiters section (Figure C).

Figure C

  1. Click Next to advance to Step 3, then click Text under Column Data Format (Figure D).

Figure D

  1. Click on the second column in the Data Preview window and then click Text under Column Data Format (Figure E).
  2. Enter D2 in the Destination text box and click Finish.

Figure E

Figure F shows the results.

Figure F


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

74 comments
lewruss
lewruss

I think you should indicate what version you are talking about, since many of us are still using Excel 2003.

Soumi
Soumi

I guess they aren't going to post the images for the article. However the PDF has the images.

seanferd
seanferd

Repeating "I can't see it", regardless of what browser you are using or your estimation of the author's ability to embed images into HTML, is not going to help. Come back later? Best advice I can give. (Aside from following the directions without the picture-book, that is.) Some of the CNET sites are undergoing upgrades and tweaking. It is an ongoing process, and negative effects may occur.

boxfiddler
boxfiddler

TR has issues at the moment. Yet another 'Can't see images' is not helpful.

scrmh
scrmh

Thank you for the information. I have been using formulas to split names. Text to columns is much easier.

eduardo (PT)
eduardo (PT)

I'm using Internet Explorer 8 RC1, and the browser is not showing the pictures in this article.

kim.beu
kim.beu

Figures A thru F did not download/show. Please advise... kim.beu@verizon.net

len.clerke
len.clerke

Might be useful information if the images appeared! Please fix.

nmihai67
nmihai67

Hi! I can not see any figures?

kz2000
kz2000

Sure would be nice to see the Figures!

blipso
blipso

Can't see the pictures/figures that illustrate the steps in the process. Isn't anyone looking at these comments?

kberkus
kberkus

no text in Figures A through F. Seems I'm not the only one. What gives?

carlosarce
carlosarce

helpful' but would be great if we can view the figures. thanks for the effort. may you keep us informed.

mitchet3
mitchet3

As stated before the example figures accompanying the test are not visible. Please correct the issue and let us know when it is updated.

edlopezpr
edlopezpr

Cannot view images for the tutorial (IE7)...Please make corrections so your subscribers can be able to visualize this recomendation.

heavener
heavener

Text-to-columns is such a valuable function I put on the command bar in Excel 2007. There are a few caveats: (1) make sure you have enough columns between other live data and the column to be parsed. Otherwise text-to-columns will overwrite some of the other data columns. I've seen text-to-columns go to six or seven columns on occasion. (2) In additioon to the others already mentioned, it will also separate compound last names (Von Meister, St. Pierre, etc.) (3) If you cut and paste anything else with a Space (even within and/or from other columns), Excel will still think you want the Space separated. Before doing additional pastes, just run text-to-columns again in one of the blank columns in Excel, making sure to chose a non-space character as the separator.

yajanssen
yajanssen

Can't see any of the samples

JARiehle
JARiehle

I cant see any of the figure in th is article moreover it seems to be a function that is ONLY available in Ecel 2007. What about Excel 2003?

smcnealy
smcnealy

I couldn't see the images either, but followed the printed directions and it's easy. I was using an existing table of several columns so when entering the cell value in the Destinaion text box I entered a cell value to the right of my data. I've had to use a convoluted workaround for YEARS to deal with this. Hallelujah!!!

Soumi
Soumi

I wrote the MS Host Susan Harkins shown as the contact above. Maybe she will get the images posted.

jccharles
jccharles

besides not being able to see the pictures, there is a step missing somewhere. This does not work. You cannot view the second column in the preview window.

atobun
atobun

Figures do not show.

tws777
tws777

can you email this so i can see the pictures

fcinco
fcinco

Can't see the Figures.

LannieC
LannieC

Cannot view figures with Firefox 3.0.1 or IE7 Using AVG Anti virus

fgagnon
fgagnon

Can't see figures, and expect it to screw up anyway with names like: Mary Ann Jones Dr. George Smith John H. White, Jr. ... etc.

lcrocilla
lcrocilla

Please re-post can't see anything

Mr. Content
Mr. Content

Not even a token response from the author or CNET staff that this has even been noticed and is being looked into.

Glenn from Iowa
Glenn from Iowa

1) I generally put the raw data (names) in a blank sheet, run text-to-columns, cleanup the data (combine names that go together, etc.), make sure I have enough columns in the sheet I'm pasting to, and paste the cleaned up data. 3) I usually have the opposite problem: when I paste data that I want separated, it usually will not run text-to-columns, or even prompt me to run it. Next time, I'll try running text-to-columns in a blank column just before pasting. 4) To verify that a column is empty (since sometimes the first value in a column is many rows down, off the screen), go to the first column you think will be empty. Use the keyboard shortcut [End],[Up Arrow] to go to the top of the column, then [End], [Down Arrow]. If the column is empty, it should go all the way from the first row to the last (65535 or 1048576, depending on your version). If not, it will stop on the first value it finds. If it's not empty, keep trying the column to the right until you find an empty column.

smcnealy
smcnealy

See my earlier post. The printed directions work easily even if the pictures aren't visible.

Joslyn.Pribble
Joslyn.Pribble

I sure hope she can. This is a great tip and will be very helpful but the images would really enhance the understanding for me.

mfarlie
mfarlie

And what about names such as Herbert Charles De La Rue? The family name is 'De La Rue'. I worked on software to solve this problem many years ago and the development was abandoned because, like Mary Ann Jones' method, it works only for simple cases. Splitting into individual parts can be done but still doesn't yield a very useful result.

Katsi
Katsi

You can set text qualifiers. Just put "" or '' on either side of the first names, but keep them consistent, so if you use "" stick with that. Then you should see it separated correctly in step 2 if you have the text qualifier you used selected.

boxfiddler
boxfiddler

as well as many others here, haven't read several posts that attempted to point out that something out of the ordinary is going on. Obviously, you're not monitoring in the least.

DBlayney
DBlayney

The method described (even without the pictures) is good. It just needs to be adapted to the data a bit and then the exceptions handled. If we assume that MOST of the names will just have two elements AND that that is all that is needed then follow the process as described. After that, perform two simple checks: 1. Are there any cells where there should be a surname but the cell is blank? (Implies that the input data had only one name in it and something is missing) 2. Are there any values in any columns to the right of the surname column? If so, there is a "complex" name and it must be manually fixed. If the norm is three elements (John H Doe and the like), the aadapted solution is obvious. If it were my problem, I would also ensure that all leading, trailing and double spaces were eliminated from the data before starting the process (the TRIM function is perfect for this).

heavener
heavener

Having to go through a list to add qualifiers around some items and not others is more work than just using text-to-columns and then cleaning up the stray ones that don't conform. My work-around is to grab the results and paste them into Notepad. I replace the appropriate Tab(s) with Space(s). Then I run text-to-columns again in one of the blank columns in Excel, making sure I chose a non-space character as the separator. I paste the Notepad data back into the same columns in Excel. The remaining Tab(s) are interpreted by Excel as separate columns (if you don't do the second text-to columns, however, Excel will still think you want the Space(s) separated).

fgagnon
fgagnon

@ Just put "" or '' Of course. -- any unique delimiters will do. ;) I was just pointing out that the example is an over-simplified case. Most lists long enough to warrant an automated solution have a significant variety of form that makes using a space as the delimiter impractical -- so it invariably comes down to trolling manually through the list to idenify prefixes ("Mr.", "Ms.", "Dr.", "Mr. and Mrs.", etc), postfixes ("Jr.", "Esq.", "III", "and Co.", etc.) not to mention compound names. :shrug:

Editor's Picks