How to properly parse out the First & Last Names fields from the Full name column

**Mason_98** · 03-14-2020, 01:03 PM

I need to fill in every empty cell in the ‘First Name’, ‘Last Name’, and ‘Email Address’ fields. You can use Test Worksheet B to append the corresponding email address for each contact in Worksheet A, but you do not have that ability with the First Name and Last Name fields. You will have to figure out how to systematically and properly parse out the First and Last Names fields from the Full Name column, as there are too many rows to simply copy/paste

I included the full excel sheet for workbook B. It is in the zip file.

**Richard Buttrey** · 03-14-2020, 01:15 PM

Maybe
B2:

Formula:

Please Login or Register  to view this content.

C2:

Formula:

Please Login or Register  to view this content.

L2:

Formula:

Please Login or Register  to view this content.

M2:

Formula:

Please Login or Register  to view this content.

**Mason_98** · 03-14-2020, 04:19 PM

The email address and Customer No. column seems incorrect. I'm getting N/A on most of them

**Richard Buttrey** · 03-14-2020, 05:35 PM

I don't see that. I see 577 of the 10,000 records have #N/A

Are you sure you've pasted the formula correctly.

See attached. I've left the formulae in L2:M2 but converted the rest of the formulae to values
I've had to save it as a .xlsb file since the .xlsx was just too large for the forum.

**Mason_98** · 03-14-2020, 06:20 PM

The original file is too large to upload that's why I shorten it

**hrlngrv** · 03-14-2020, 08:36 PM

Unfortunately, parsing names CORRECTLY into first and last names given all possible last name forms, even ignoring Chinese and Korean names putting family (last) name first, is one of the hardest things there is to automate. In your A workbook, cell D101 shows ABDUL AL HALABI, and cell D106 shows CHOWDHURY AL-AMIN. I'd be willing to bet LOTS of money the D101 last name is AL HALABI. The issue is that there are LOTS of multiple word last names for those of European, Middle Eastern and Native American descent. You have some Spanish or Portuguese surnames starting in D2073 with IZZY DE ANDA, and D2072 is almost certainly another instance of a missing last name. Dutch/German multiple word surnames begin at cell 9132.

Cell D352 shows ANDREW B. I'd also be willing to bet LOTS of money B isn't that person's last name. Meaning you have data quality problems which no formula or macro can fix. Another type of data problem is cell D2869 showing JODELLE FRANKLIN 108-275. Yet another, cell D6675 showing DEPARTMENT OF STATE.

The most sensible approach is to assume only 2 word names with the 2nd name longer than 1 character spilt easily. ALL OTHER NAMES need to be split manually. Or there are Python modules with LOTS of logic and pattern matching databases for finding LIKELY multiple word last names.

Anyway,

B2: =IF(IF(LEN(D2)-LEN(SUBSTITUTE(D2," ",""))=1,LEN(SUBSTITUTE(REPLACE(D2,1,FIND(" ",D2),""),".",""))>1),LEFT(D2,FIND(" ",D2)-1),#N/A)
C2: =IF(ISTEXT(B2),TRIM(SUBSTITUTE(D2,B2,"")),B2)

Select B2:C3 and fill down as far as needed. The formulas which return #N/A you'll need to parse manually.

**BMV** · 03-15-2020, 01:07 AM

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

And of course there are can be exclusion that described hrlngrv. But part of then can be solved by substitution also. For example for AL-

Please Login or Register  to view this content.

**Bo_Ry** · 03-15-2020, 01:50 AM

Please try at
B2
=LEFT(D2,FIND(C2,D2)-2)

C2
=TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",20)),20))

Email and
L2:M2
=VLOOKUP($A2,'[Test Worksheet B- Copy.xlsx]Sheet1'!$A$2:$C$11168,COLUMNS($K2:L2),0)

There are many N/A because no Contact ID in Test Worksheet B- Copy eg 344418

**Mrrrr** · 03-15-2020, 03:56 AM

This also works in B and C columns:

B2 =LEFT(D2;LEN(D2)-LEN(C2)-1)
C2 same formula as Bo_Ry's post above.

Drag down.

As for columns L & M I get over 7500 #N/A.
No idea how Richard Buttrey got only 577 #N/A.

**Richard Buttrey** · 03-15-2020, 06:15 AM

Originally Posted by Mrrrr

This also works in B and C columns:

B2 =LEFT(D2;LEN(D2)-LEN(C2)-1)
C2 same formula as Bo_Ry's post above.

Drag down.

As for columns L & M I get over 7500 #N/A.
No idea how Richard Buttrey got only 577 #N/A.

Using your original file containing 10000 records I copied the L2:M2 down all 10000 rows.
Then filtered the file for #N/A on column L selected all the fultered records and noted the number 577 in the status bar at the bottom of Excel. And of course a normal

Formula:

Please Login or Register  to view this content.

returns the same number.

That's how I got the 577.
As you can see in the workbook I returned where I'd range valued all ex ept L2:M2

**Mason_98** · 03-15-2020, 12:31 PM

I have updated and included the full excel sheet for workbook B. It is in the zip file. Please see if the functions will now work for all columns

**Mrrrr** · 03-15-2020, 06:41 PM

Originally Posted by Richard Buttrey

That's how I got the 577.
As you can see in the workbook I returned where I'd range valued all except L2:M2

Well for example for the 2nd Contact ID which is 344418 I could not find a match with my formula (which was a normal INDEX-MATCH).

I can only now find a match - when Mason_98 added the full workbook B - at row 44762 and the result is different from what your workbook shows:
- your wkb in post #4: 344418 [email protected] CNT153610
- Mason_98 latest update: 344418 [email protected] CNT008592
- I got #N/A on that Contact ID with the previous version of Workbook B

I no longer have the previous Workbook B to show you what I got, that I had no match for 344418 Contact ID.

But maybe Mason_98 changed the workbook more than once which means you had a different version than I had... this is the only thing that would explain it really...

Now with the full 48k rows Workbook B I get 7 #N/As in total.

**Mason_98** · 03-15-2020, 07:15 PM

Can you please show me your formulas with the updated workbook B please. The end result in Workbook A should have 10,001 rows with header row included. There are several duplicates (the exact same Full Name, Job Title, Company Name, Address, City, State, Post Code, Country).

**Richard Buttrey** · 03-16-2020, 08:58 AM

Originally Posted by Mason_98

Can you please show me your formulas with the updated workbook B please. The end result in Workbook A should have 10,001 rows with header row included. There are several duplicates (the exact same Full Name, Job Title, Company Name, Address, City, State, Post Code, Country).

To whom is this addressed.

**Richard Buttrey** · 03-16-2020, 09:04 AM

If #13 was addressed to me then from myour zip file

L2:

Formula:

Please Login or Register  to view this content.

M2:

Formula:

Please Login or Register  to view this content.

B2 & C2 as previously mentione din #2 although I see there are some smaller versions which give the same result.

**Mrrrr** · 03-16-2020, 12:13 PM

For B2 and C2 you could use the shorter formulas from posts #8 (Bo_Ry) or #9 (myself).

How to properly parse out the First & Last Names fields from the Full name column

LinkBack

Thread Tools

Rate This Thread

Display

How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Re: How to properly parse out the First & Last Names fields from the Full name column

Thread Information

Users Browsing this Thread

Similar Threads

Matching Abbreviated names and Full names

Cannot set column names properly in excel basic chart

VBA to parse a text string containing three fields of different lengths

Search a range Last Names for age & return the value to a List of FULL NAMES

I need Help to parse address city state zip and country if applicable for all fields

I have a column full of names and need to randomly pick 300 of them.

How to parse date/time stamp into different fields?

Bookmarks

Bookmarks

Posting Permissions