Extracting Names

ChrisFoster · Jun 29, 2019

Hi All,

I am hoping one of you clever clogs can help me.

In column A, I have a a list of names. Some have middle names and some have double barrel surnames.

What I want is a formula or VBA code that extracts the data from that column and presents it as follows;

PHP:

Title  Forename  Surname

It needs to be able to recognise double barrel surnames as well please - they will all be separated by a dash, like this Torres-Washington.

Cheers

Chris

NdNoviceHlp · Jun 29, 2019

You didn't give a good example of what your input is or what your output should look like. See the Split function. Here's an example that you can use as a ws function if you put this in module code...

Code:

Public Function NameSplitter(Name As String) As String
'eg. Bob Mason-Jar Engineer
Dim TempArr As Variant
TempArr = Split(Name, " ")
NameSplitter = TempArr(2) & ", " & TempArr(0) & " " & TempArr(1)
End Function

If your data was in A1 then you would use this by...

Code:

 = NameSplitter(A1)

HTH. Dave

kweaver · Jun 29, 2019

Chip Pearson wrote a page about parsing names (but in a different format as you have). Worth checking however.

http://www.cpearson.com/Excel/FirstLast.htm

kweaver · Jun 29, 2019

ChrisFoster:

There are a number of issues one faces when trying to do this.
1) do the names ALL have a "title"?
2) are the "titles" the same format? e.g., Dr., Ms., Miss (no period), etc.
3) if they have a middle name and/or initial, with the initials all have a period after the initial?
4) could there be a suffix e.g., Jr., Sr., III (no period)

IMHO, this is a complicated task as there are several options to consider and possibly no consistent structure to the data.

kweaver · Jun 29, 2019

Well, here's an ugly stab at this with formulas. I haven't cleaned it up, but it seems to work for the cases I tried. What do you think?

Book1

A

B

C

D

E

F

G

H

I

J

1

Original

Title

First Name

Middle whatever

Last Name w/o suffix

Last Name w/Suffix

Helper1

Helper2

Helper3

Use this for middle

2

Ms. Mary Jane Brown-Jones

3

Margaret Thatcher

Margaret

Thatcher

Margaret

0

8

4

Mrs. Anastasia Jones

Mrs.

Anastasia

Jones

Mrs. Anastasia

1

14

5

Kevin R. Weaver

Kevin

R.

Weaver

Kevin R.

1

6

R.

6

Archie Harrison Mountbatten-Windsor

Archie

Harrison

Mountbatten-Windsor

Archie Harrison

1

7

Harrison

7

Archie Harrison Mountbatten-Windsor, II

Archie

Harrison

Mountbatten-Windsor

Mountbatten-Windsor, II

Archie Harrison

1

7

Harrison

8

Lord Kevin R. Weaver-Smith

Lord

Kevin

R.

Weaver-Smith

Lord Kevin R.

2

11

R.

9

Mr. Kevin Weaver-Smith

Mr.

Kevin

Weaver-Smith

Mr. Kevin

1

9

10

Miss Jane Doe

Miss

Jane

Doe

Miss Jane

1

9

11

Mr. Kevin R. Weaver, Jr.

Mr.

Kevin

R.

Weaver

Weaver, Jr.

Mr. Kevin R.

2

10

R.

Sheet12

Cell Formulas
Range	Formula
B2	=IF(OR("Mrs." =LEFT($A2,4),"Lord"=LEFT($A2,4),"Miss"=LEFT($A2,4)),LEFT($A2,4),IF(ISERROR(FIND(".",$A2)<=3),"",IF(FIND(".",$A2)<=3,LEFT($A2,FIND(".",$A2)),"")))
C2	=IF(""<>$B2,MID($A2,1+FIND(CHAR(160),SUBSTITUTE($A2," ",CHAR(160),1)),((FIND(CHAR(160),SUBSTITUTE($A2&" "," ",CHAR(160),2))))-(1+FIND(CHAR(160),SUBSTITUTE($A2," ",CHAR(160),1)))),LEFT($A2,(FIND(CHAR(160),SUBSTITUTE($A2," ",CHAR(160),1))-1)))
D2	=J2
E2	=IF(ISERROR(FIND(",",$A2)),TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),LEN($A2))),TRIM(RIGHT(SUBSTITUTE(LEFT($A2,FIND(",",$A2)-1)," ",REPT(" ",LEN(LEFT($A2,FIND(",",$A2)-1)))),LEN(LEFT($A2,FIND(",",$A2)-1)))))
F2	=IF(ISERROR(FIND(",",$A2)),TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),LEN($A2))),TRIM(RIGHT(SUBSTITUTE(LEFT($A2,FIND(",",$A2)-1)," ",REPT(" ",LEN(LEFT($A2,FIND(",",$A2)-1)))),LEN(LEFT($A2,FIND(",",$A2)-1))))&RIGHT($A2,LEN($A2)-FIND(",",$A2)+1))
G2	=LEFT($A2,FIND($F2,$A2)-2)
H2	=LEN(LEFT($A2,FIND($F2,$A2)-2))-LEN(SUBSTITUTE(LEFT($A2,FIND($F2,$A2)-2)," ",""))
I2	=LEN($B2)+LEN($C2)+LEN(LEFT($A2,FIND($F2,$A2)-2))-LEN(SUBSTITUTE(LEFT($A2,FIND($F2,$A2)-2)," ",""))
J2	=RIGHT(LEFT($A2,FIND($F2,$A2)-2),LEN(LEFT($A2,FIND($F2,$A2)-2))-I2)

Extracting Names

ChrisFoster

Active Member

Excel Facts

NdNoviceHlp

Well-known Member

kweaver

Well-known Member

kweaver

Well-known Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

Extracting Names

ChrisFoster

Active Member

Excel Facts

NdNoviceHlp

Well-known Member

kweaver

Well-known Member

kweaver

Well-known Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock