Solved

Separate name into 3 columns

Posted on 2014-03-26
6
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 8

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 8

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

756 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