?
Solved

Separate name into 3 columns

Posted on 2014-03-26
6
Medium Priority
?
202 Views
Last Modified: 2014-03-31
I have a sheet with the name in Column A like this:

Barnes, Henry L

I need a formula to separate this into three columns: one for the last name, one for the first name, and one for the initial.

Actually (and preferably), I do not need the middle initial. It would be better to pull just the first and last names into a new single column so it looks like this in the new column:

Henry Barnes

The second alternative is preferred if possible. If not, then I can use the first alternative and just delete the middle initial column after the formula runs and concatenate the first two columns into one.

Thanks!

Glenn Stearns
0
Comment
Question by:Glenn Stearns
  • 4
  • 2
6 Comments
 
LVL 9

Expert Comment

by:Lee Ingalls
ID: 39956054
From the Data Tab select Text to Columns.
Select Delimited, Next then click Comma and Space. Next until Finish.
Text-to-Columns.pdf
0
 

Author Comment

by:Glenn Stearns
ID: 39956201
Lee...

Thanks for the information. Unfortunately, the raw data I'm dealing with did not put a comma in between the first name and initial. The name in the example is 'Barnes, Henry L', with no comma between 'Henry' and 'L', although there is a comma between 'Barnes' and 'Henry'. Thus using the comma delimiter in Text to Columns will not work since the use of the comma is inconsistent. This is why I need a formula that will take this into consideration. I need to strip the middle initial from the result as I do not need it. The complementary data table I will be using with VLOOKUP only needs the first and last name to extract the data.

Glenn Stearns
0
 
LVL 9

Expert Comment

by:Lee Ingalls
ID: 39956235
You don't need the comma after Henry  --  that's where we use the space delimiter.
You could always groom the data by doing a Find and Replace to get the data delimited consistently.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

Author Comment

by:Glenn Stearns
ID: 39956448
Lee...

The problem with text to columns is that it is cumbersome to do it that way. I need a cell formula for column A to place a result in column B so that I can make the process efficient as I have to do this every day.

Let's try it another, slightly different way that might be easier:

Column A contains 'Barnes, Henry L'. The formula or formulas just need to take 'Barnes, Henry L'  (as it appears in Column A) and give me 'Barnes, Henry' (without the 'L' and with the comma) in column B, i.e., 'Barnes, Henry' in column B. (The comma after the last name 'Barnes' is fine to leave in.)  I just need to strip the middle initial off the end for those names that have a middle initial. For all the others, such as 'Allen, Christopher', with no middle initial, the formula would leave this unchanged and put 'Allen, Christopher' in column B.

If I can do this, then I can construct a working VLOOKUP between the two sheets.
0
 

Accepted Solution

by:
Glenn Stearns earned 0 total points
ID: 39956691
I found this formula elsewhere on the Web and it does exactly what I need it to do:

If 'Barnes, Henry L' appears in B3, then put this formula in A3:

=IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=1,B3,SUBSTITUTE(LEFT(B3,FIND("^^",SUBSTITUTE(B3," ","^^",2))-1),",","",2))

The result will be 'Barnes, Henry'. For any name without a middle initial on the end, it returns just the 'Lname, Fname'. It does not work if the last name is 'Barnes, Jr.' due to the extra comma before 'Jr', but it does standard name constructions. I can live with that as I can manually fix any 'Jr' that exists in the table.
0
 

Author Closing Comment

by:Glenn Stearns
ID: 39966138
I did some other research on the Web and found a working formla.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question