Solved

Separate name into 3 columns

Posted on 2014-03-26
6
189 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:glennes
  • 4
  • 2
6 Comments
 
LVL 7

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:glennes
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 7

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:glennes
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:
glennes 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:glennes
ID: 39966138
I did some other research on the Web and found a working formla.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
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…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now