• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Separate name into 3 columns

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
Glenn Stearns
Asked:
Glenn Stearns
  • 4
  • 2
1 Solution
 
Lee IngallsDirector of IT/TS, Quality and FinanceCommented:
From the Data Tab select Text to Columns.
Select Delimited, Next then click Comma and Space. Next until Finish.
Text-to-Columns.pdf
0
 
Glenn StearnsAnalystAuthor Commented:
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
 
Lee IngallsDirector of IT/TS, Quality and FinanceCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Glenn StearnsAnalystAuthor Commented:
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
 
Glenn StearnsAnalystAuthor Commented:
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
 
Glenn StearnsAnalystAuthor Commented:
I did some other research on the Web and found a working formla.
0

Featured Post

Technology Partners: 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!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now