Extracting last, first middle name

**sbjmn0** · 02-03-2009, 12:07 AM

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.

**arthurbr** · 02-03-2009, 03:08 AM

Originally Posted by sbjmn0

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.

Hi and welcome to the board,
as this looks a lot like homework, I suggest you try this forum's "search" function.
This question has been answered lots of times.
Good luck

**sbjmn0** · 02-03-2009, 10:50 PM

thanks but that really doesnt help ive tried many of teh formulas given in the different threads and all of them just give me #value and none of the problems in teh threads followed teh same format.

**davesexcel** · 02-03-2009, 10:56 PM

Originally Posted by sbjmn0

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.

What have you tried so far
Here is one part, to extract the first word from a string in A4
=LEFT(A4,FIND(" ",A4)-1)

**sbjmn0** · 02-03-2009, 11:03 PM

I already have the last name, and middle initial my problem is i cannot get teh first name alone. =LEFT(B8,FIND(" ",B8,1)-1) i used that and found all teh last names, =IF(L8,H8,"") for the middle inital. but im stuck finding a formula to find the first name which comes after the last name in my data, so essentially it goes last name, first name middle inital. thank you.

**sbjmn0** · 02-03-2009, 11:20 PM

okay so i figured out using this equation: =IF(LEN(B8)-LEN(SUBSTITUTE(B8," ",""))>1,MID(B8,FIND("",B8)+8,FIND(" ",B8,FIND(" ",B8)+1)-(FIND(" ",B8)+1)),"") i can find the first name however the persons without a middle name, when i enter this formula it does not yield a first name just a blank space.

**sbjmn0** · 02-03-2009, 11:26 PM

ugh please help anybody haha this is so frustrating.

**sbjmn0** · 02-03-2009, 11:41 PM

bump, come on guys i need this done by midnight any help would be appreciated.

**tbrinkley** · 07-16-2009, 12:48 PM

I know this might be a little late, but this thread may be helpful to others. I created a Formula that works pretty good for extracting the last, first and middle names from a column that has them all together with the last name separated by a comma.

=IF(IFERROR(FIND(" ", A1, SEARCH(" ",A1)+1),), TRIM(MID(A1,SEARCH(" ",A1), SEARCH(" ",A1,SEARCH(" ", A1) + 1) - SEARCH(" ",A1) ) ), TRIM(REPLACE(A1, 1, SEARCH(",", A1, 1), "")))

The above formula extracts the first name "Aimy" from "Smith, Aimy A". It works 99 percent of the time as long as the names are separated by a comma and a space e.g. ", ". However, there are some complicated names that it does not work on such as: "Doe Jr., Rudy E."

=IF(IFERROR(FIND(" ", A1, SEARCH(", ", A1)+1), ), TRIM(MID(A1, SEARCH(", ", A1) + 1, SEARCH(" ", A1, SEARCH(" ", A1) + 1) - SEARCH(" ", A1))), TRIM(REPLACE(A1, 1, SEARCH(", ", A1, 1), "") ) )

The above formula extracts "John" from "Van Brown, John B". It works to get the first name from names with spaces in the last name.

=IF(IFERROR(FIND(" ", A1, SEARCH(" ",A1) + 1),), RIGHT(A1, LEN(A1) - SEARCH(" ", A1, SEARCH(" ",A1) + 1)), "")

The above formula extracts the middle name "A" from "Smith, Aimy A".

=TRIM(LEFT(A1,FIND(",",A1)-1))

The last one above extracts the last name, “Doe”, from “Doe, John G”

**jenziepie** · 08-14-2014, 07:32 PM

You are a lifesaver, thank you SO MUCH! This is fantastic!

Extracting last, first middle name

LinkBack

Thread Tools

Rate This Thread

Display

Extracting last, first middle name

Re: Extracting last, first middle name

Re: Extracting last, first middle name

Re: Extracting last, first middle name

Re: Extracting last, first middle name

Re: Extracting last, first middle name

Re: Extracting last, first middle name

Re: Extracting last, first middle name

Re: Extracting last, first middle name

Re: Extracting last, first middle name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions