Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Separate name into 3 columns

Posted on 2014-03-26
6
Medium Priority
?
196 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
[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 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

721 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