Free Microsoft Excel 2013 Quick Reference

Extracting last, first middle name

So I have a set of about 180 names listed in the following manner:
Last name, first name middle initial. We are not allowed to separate the parts into different columns but rather we have to set up three columns that will extract the three different categories. Say the name is Johnson, Alan A, we would have to extract each part into three different columns. However I have been having a hard time extracting the first name. I have found all of the middle initals and those without one have displayed not having one. However when I do a count of how many people have middle names all of them come out in the data even if it theres a black space. Any help would be greatfully appreciated. thank you.


Post your answer or comment

comments powered by Disqus
Hey all,

Basically I have one cell that can look like this:

IN RIGHT OF AKA PAUL A HERMAN,

And I want to have it look like this:

HERMAN PAUL A

So:

1) extract content to right of AKA
2) If comma exists at end, remove it
3) Rearrange the name so it's last, first, middle rather than first, middle, last

I have been working on this and this is what I have:

Sub Inspect()
    Dim old_str As String
    Dim new_str As String
    Dim first_name As String
    Dim last_name As String
    Dim middle_name As String
    Dim regex As Object
    Dim Rng As Range
    
    pos = InStr(Range("A1"), "AKA")
    
    If pos <> 0 Then
           old_str = Right(Range("A1"), Len(Range("A1")) - pos - 2)
    End If
    
    new_str = Left(old_str, Len(old_str) - 1)
    
    Range("B1").Value = Trim(new_str)
    
    Set Rng = Range("B1")

    Set regex = CreateObject("VBScript.RegExp")

    regex.Global = True
    regex.IgnoreCase = True
    regex.Pattern = "(^w+sw+sw+)"

    If regex.Test(Range("B1").Value) Then
    
        first_name = Left(B1, Find(" ", B1) - 1)
        
        middle_name = Mid(B4, Find(" ", B4) + 1, Find(" ", B4, 1 + Find(" ", B4)) - Find("
", B4) - 1)
    
        last_name = Right(A1, Len(A1) - Find("*", Substitute(A1, " ", "*", Len(A1) -
Len(Substitute(A1, " ", "")))))
    End If
    
    Range("C1").Value = last_name & " " & first_name & " " & middle_name
End Sub
I get compiler error and it highlights the FIND function. It appears I cannot use excel formulas in a VBA script. Anyone have any solution how I can extract the first, last, and middle so I can rearrange them in my VBA function?

Thanks for response

Hi All,

I want to extract the last/first/middle name - Jones John Albert. The list is not a fixed list (the names will change). The problem is that I want just the names and not the suffixes. For examples Jones John Albert Jr. The formulas that I found select the left name/middle/right which would give me the Jr. instead of Albert. The other issue that could arise is for a spouse that chooses to retain their maiden name- Johnson Weaver Janet Margaret. Can anyone think of a way to ensure last/middle/first each time?

Regards,

Mike

I am trying to create a script that will query a government website and essentially replicate a subset of it’s data so that I can use it in pivot tables etc. The query tool provided by the website actually works quite nicely in the way it accepts the queries and presents the data however it limits the displayed results to 50 records.

My goal then is to figure out how to extract all the records no more than 50 at a time. I also don’t want to miss any.

The database field I am using as a search key is a name field consisting of individual’s last, first, middle names. Hit’s are returned with the full name of the individual and a company name and address which is what I’m really after. [I also need to automate the copying of the data once it’s displayed and pasting it to Excel but that will be my next question which I think I can find the answer to in the help section]

This is where it gets tricky. If I submit a query with ‘Ian’ I will also get Adrian and Brian and Giancarlo etc.

The smallest query allowed is 3 characters. So at first I thought ‘all I have to do’ is submit the shortest number of 3 digit sequences required to match all the possible 3 digit letter combinations in a name, or to be more precise, the shortest number of 3 digit combinations required to match just one 3 digit combination in a name since searching for Bri would return all the Brians but so will searching on ‘ria’ and ‘ian’. So that approach will actually make the problem worse because 3 digits searches will constantly result in hits containing more than 50 records. So I guess the real solution would be to use the fewest number of 4 and 5 digit combinations as well.

Has anyone come across this scenario before? I expect it’s more of a math problem than anything else but I don’t know of any math forums like Oz!! I’m very open to less than perfect solutions that capture the bulk of available names and/or duplicate many of the results making cleaning of the data necessary.

One more piece of info on the query tool, queries can be refined by entering an address or part thereof. For example; searching on Dave returns 170 hits. If I then search on Dave plus ‘par’ I will get just the Dave’s in Paris for example. Partial postal (zip) codes can be searched as well which would allow for a more systematic approach no doubt. [sorry if my title broke the rules but I couldn't think of anything shorter that would fit & make sense!]

Hello

I use this formula in many of my spreadsheets for last/first/middle name:

=CHAR(10)&
UPPER(IF(C4="","",C4&", "&C5&IF(C6="",""," "&C6)&REPT(CHAR(10),2))
&IF(D4="","",D4&", "&D5&IF(D6="",""," "&D6)&REPT(CHAR(10),2))
&IF(E4="","",E4&", "&E5&IF(E6="",""," "&E6)&REPT(CHAR(10),2))
&IF(F4="","",F4&", "&F5&IF(F6="",""," "&F6)&REPT(CHAR(10),2))
&IF(G4="","",G4&", "&G5&IF(G6="",""," "&G6)&REPT(CHAR(10),2))
&IF(H4="","",H4&", "&H5&IF(H6="",""," "&H6)&REPT(CHAR(10),2))
&IF(I4="","",I4&", "&I5&IF(I6="",""," "&I6)&REPT(CHAR(10),2))
&IF(J4="","",J4&", "&J5&IF(J6="",""," "&J6)&REPT(CHAR(10),2))
&IF(K4="","",K4&", "&K5&IF(K6="",""," "&K6)&REPT(CHAR(10),2)))

Is there a more concise formula for this since it is so repetitive?

Thanks for any help!

VR/Lost

I've uncovered excellent text conversion formulas to extract both the first name and last name from an 800+ column of names that look like:
Davy J Jones
Marcus Welby
Anthony Lazy Denosso
Janis Joplin
Mark T Seager
Mary Jean Post
Some fields have middle names an/or initials, but not all. I cannot find a formula to extract the middle name and/or initial, that also will result in a blank field if there is no middle name/initial.

See attached test file....

Can anyone help?

What is the formula to combine 3 colums into one? I need to merge First, Middle and last names together.:biggrin:

I want to extract a person's last name from a field & put it into another for
sorting purposes. If their name is Bud Line, I want to put Line into a
different field. If their name is Betsy Sue Color, I want to put Color into
a different field. I think their should be a way to search for the space
starting from the right side, but I don't know how. The problem I'm having
is if their name is listed as first, middle and last.

I want to extract a person's last name from a field & put it into another for
sorting purposes. If their name is Bud Line, I want to put Line into a
different field. If their name is Betsy Sue Color, I want to put Color into
a different field. I think their should be a way to search for the space
starting from the right side, but I don't know how. The problem I'm having
is if their name is listed as first, middle and last.

Need to seperate First Middle and Last Name

I have data containing the data in the form of First, Middle and Last Name usually, the text will only have spaces in between the words.

I know the formula for getting the First and Last name...

First Name = LEFT(A2,LEN(A2)-FIND(" ",A2,1))

Last Name = RIGHT(A2,LEN(A2)-FIND(" ",A2,1))..

But there are times when we have the data in four words.
Ex:- Dr Humprey Davis Bogart

then I want to seperate the four words into four seperate columns.
as in
B2 ---> Dr
C2 ---> Humprey
D2 ---> Davis
E2 ---> Bogart

Hello,

I have a spreadsheet that has a bunch of doctors, but there are several duplicate doctors, i need to eliminate the dupes. I have first names in column E, Middle Names in column F, and Last name in Column G. I have attached an example spreadsheet to help, there is no sensitive information here. Any help would be greatly appreciated.

Thank you,

Seth.

Make formatted name in one column from "last, first MI" to three seperate
columns of: 1st col-First name"first", 2nd col-Middle"middleOrMI" and 3rd
col-Last name"last". When you have a file with several rows of names you
would like to do this to, how do you do it? Going through it individual row
by row would be time consuming and monotonous.

I have a column of names, most of which are FirstName LastName in format.
Some, however are First MiddleName (or MI) Last. I need to convert these
values into LastName Firstname MI. I have a formula that gets close, but
doesn't account for the possible presence of a Middle Name or initial.
For ex, data could be Jill Smith, or Jo Ann Smith. I need to make Smith,
Jill and Smith, Jo Ann.

Seems like I need to go backwards thru the string with a Search function to
find the 2nd space for this to work, but can't. Or should I try it by parsing
consecutive columns with incremental changes together???? Help!

Thank you

I have 3 columns of names (first, middle and last) but each row is in a different order.

ie-

first last middle
last first middle
middle last first
last middle first
...

I have another list of names (on the 2nd sheet) in the correct order.

I need to rearrange the first list into the correct columns,
being careful of people with the same last names.

The second list is shorter as some people no longer work here, also the middle names are generally not known, they dont matter as much.

I know it may not be possible to sort them all out but how much can be done?

See the attached workbook.

Scenario "Separating Full Name in Column A into three columns, one for First Name, Middle Name, and Last Name" ... E.g.: Column A: "FirstName MiddleName LastName"; Result Column B: "FirstName"; Column C: "MiddleName" and Column D: "LastName".

How can on do this?

I am trying to download data from Crystal Reports into an Excel spreadsheet. I am confronted with 2 problems.

1. The Crystal database has an emergency contact full name like Jane Doe in 1 field. The new Excel worksheet needs to divide the first and last name into separate fields like Jane in column 1 and Doe in column 2. I have 150 name and the number of letters in each name varies considerably.

2. I also have an employee's name downloaded from Crystal into 3 columns: first name, middle name, and last name. I need to convert the middle name into a middle initial in the new spreadsheet. How do I do that?

Thank you for your help in advance.

Hi,

I am looking to accomplish the following:

In A3 is a surname, in B3 is a first name (and possibly multiple middle names, separated by a space). In C3 I'd like the first name ONLY and the surname

A3......................B3.................C3
Hobbs.................Jon Peter........Jon Hobbs
Peters.................Mark..............Mark Peters
Jones..................Bob Tim Mark...Bob Jones

In some cells the format (all in the same cell) is:

A3
Hobbs Peter

and I'd like it to read Peter Hobbs. Suggestions to solve both these concatenate problems are welcome.

Thank you.

I received a file from an outside vendor with names listed Last, First. I
need to print labels for mailing from this file with names First Last. Is
there a way to reverse the order in an Excel spreadsheet?

ANYONE KNOW, OTHER THAN CUT AND PASTE, A METHOD OF "flip flopping" names in a
list, from last, first to first last? (maybe "telling"the list to separate
into 2 columns the words separated by the comma, last in one column and first
in another). I have a list of 360 names and not looking forward to cut and
paste this list. HELP!

Hi

My name is Robert

I'm new to this forum and would very much like someone to assist me.

INFORMATION I HAVE
I have a long list of names on one column. Some names are just made up of firstname and surname. Others have a middle name, sometimes even two middle names.

This is what the list looks like (the first 7 of 45,000 names). Need to get rid of the middle names (italic, underlined):

Aaran Naddan
Aaron Bryce Tornelle
Aaron Gibb Bryan Holy
Aaron Howard
Aaron JonnaPeter Ganjel
Aaron Kat
Aaron Lyall Godings

I need the list to look like this (obviously trying to identify all of them within the 45,000 will be quite a task):

Aaran Naddan
Aaron Tornelle
Aaron Holy
Aaron Howard
Aaron Ganjel
Aaron Kat
Aaron Godings

PROBLEM
What I'd like to do is simply delete these middle names so that all I'm left with is the firstname and surname.

SOLUTION?
Is there a formula that can identify and delete these middle names?

ASSUMPTION
Assuming of course, that the firstname in the cell is the person's first name, and the last name in the cell is infact the person's surname.
Thanks in advance for your help, it'll be great if there is someone out there who can assist me.

Cheers
Robert

In the past, I have had relatively short lists of NAMES with very random content that was reasonable easy to parse into individual components manually. But, simular to my last post, I am now anticipating being asked to solve that same challenge for a very long list of names, probably over 1000, which makes the manual method much to tedious and time consuming.

Heres the challenge: The single NAME field (column A) contains random text data, but always in the same order, from which all individual components will need to be extracted into individual columns, namely PREFIX (column B), FIRST NAME (column C), MID NAME (column D), LAST NAME (column E), SUFFIX (column F), and finally FIRST&LAST NAME only (column G). The randomness will look like:
Dr. Dork M. Doodle Sr. MD
georgie burns Jr.
jeannie g Downrigger DDS
Richard Rodgers
Ms. patsy legend kline
mr.Silly Lazy Dufus PhD
Since some names are capitalized properly, others are all capitalized and others are all lower case, all extracted data should be changed to proper case, except for the suffix column.

I can't fathom a solution for this challenge - can anyone help?

ANYONE KNOW, OTHER THAN CUT AND PASTE, A METHOD OF "flip flopping" names in a
list, from last, first to first last? (maybe "telling"the list to separate
into 2 columns the words separated by the comma, last in one column and first
in another). I have a list of 360 names and not looking forward to cut and
paste this list. HELP!

First, thanks to all who help me out. OK, here's the problem.

I have a worksheet of names, addresses, and other info. A person's name is in the following format in a single cell A1:
_][___A______]
1][Doe, John Q.]

I want this name to be divided into three seperate cells to read
_][__A__][_B_][_C_]
1][ John ][ Q. ][ Doe ]

I should also mention that not all the names include all parts and say just:
_][___A____]
1][Doe, John]
or
_][_A_]
1][Doe]

I already saw the functions that "extract" the first, middle and last names. But, they require the original cell to remain. I need a function that will do what I would have to do for days using cut and paste to truly divide the data into 3 columns, not just return its value in three columns. Are there any options here? If functions won't do it, what other choices have I?

I deal with an Excel file that has many names on it all in 1 cell.
Some of the common combinations are below. Is there any way to
separate the 1 column in to 2 columns bases on table below.

FIRST LAST CONVERT TO FIRST LAST

FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST

FIRST M LAST CONVERT TO FIRST M LAST

FIRST M. LAST CONVERT TO FIRST M. LAST

FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX

FIRST MIDDLE LAST SUFFIX
CONVERT TO FIRST MIDDLE LAST SUFFIX

FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX

The problem I have is that all the names are different lengths.

Thanks for your help.

am trying to take data from a worksheet and create a macro to copy the data, format it and place it in a different worksheet.
The data worksheet has column headerssuch as acct. Name1 number address city St Zip Phone. I need to take the data and copy it to a new worksheet.
I need to add a column called lettercode and have it prompt for a letter code from the user that is populated down the entire column. Then the acct. columnn needs to be pasted under the number column. The Name1 Column needs to become two column firstName_MiddleName and Lastname. The Name1 one first middle name or middle initial and last name that need to be seperated. Then the address city St Zip Phone columns are then copied.
I have been searching and that found some code to start with but it doesn't get me where I want to be.

CODE I FOUND TO COPY A COLUMN TO AN WORKSHEET.
Sub copy2()
Dim wb As Workbook, ws As Worksheet, wsDest As Worksheet
Dim rLook As Range, rCell As Range, sCell As String
Dim iCnt As Long, sFirstAddy As String
Const strColHead As String = "Yellow" 'your column header
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
Set rLook = ws.Rows(1)
Set rCell = rLook.Find(what:=strColHead, after:=rLook.Cells(rLook.Rows.Count, rLook.Columns.Count), searchdirection:=xlNext,
MatchCase:=True)
sCell = rCell.Address
iCnt = 1
Set wsDest = wb.Worksheets.Add(after:=ws)
Do Until sCell = vbNullString Or sCell = sFirstAddy
If sFirstAddy = vbNullString Then sFirstAddy = sCell
ws.Columns(rCell.Column).copy Destination:=wsDest.Columns(iCnt)
iCnt = iCnt + 1
sCell = vbNullString
Set rCell = rLook.FindNext(after:=rCell)
sCell = rCell.Address
Loop
Application.CutCopyMode = False
CODE I TRIED TO USE TO SPLIT NAME1 COLUMN HOWEVER IT DOESN'T WORK WELL.
Sub split()

Dim MySheet As Worksheet
Dim RowNumber As Integer

Const ColumnA = 1
Const ColumnB = 2
Const ColumnC = 3
Set MySheet = Workbooks("Book1.xls").Worksheets("Sheet1")
With MySheet
RowNumber = 1
While Not IsEmpty(.Cells(RowNumber, ColumnA))
.Cells(RowNumber, ColumnB) = Left(.Cells(RowNumber, ColumnA), 3)
.Cells(RowNumber, ColumnC) = Mid(.Cells(RowNumber, ColumnA), 4)
RowNumber = RowNumber + 1
Wend
End With

'Application.Quit

End Sub
FORMULAS I USED TO BREAK APART NAME A COLUMN.
=IFERROR(LEFT(B2,FIND(" ",B2)-1),B2)
=IF(LEN(b2)-LEN(SUBSTITUTE(b2," ",""))>1,MID(b2,FIND(" ",b2)+1,FIND("
",b2,FIND(" ",b2)+1)-(FIND(" ",b2)+1)),"")

=IFERROR(RIGHT(B2,LEN(B2)-FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-
LEN(SUBSTITUTE(B2," ",""))))),"")
=CONCATENATE(I2," ",J2)

Thanks


No luck finding an answer? You could always try Google.