how to extract away middle initial when the last name contains a space and the cell contains no commas

[johnmerlino]

Hey all, this formula almost does the job:

=IF(FIND(" ",A1&" ")-LEN(A1)-1,LEFT(A1,FIND(" ",A1)+FIND(" ",TRIM(
MID(A1,FIND(" ",A1)+1,255))&" ")),LEFT(A1,FIND(" ",A1&" ",FIND(" ",A1&" ")+2)))

But as you can see in below example, when the last name contains a space (e.g. Martin Marie Anne Hernara), it clips away Anne Hernara, just leaving Martin Marie. I want it to return Martin Marie Anna and just clip out the Hernara at end.

<table border="0" cellpadding="0" cellspacing="0" width="521"><col style="width: 343pt;" width="457"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 343pt;" width="457" height="20">AVERNA ROBERT C </td> <td style="width: 48pt;" width="64">AVERNA ROBERT </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Corn Marlin</td> <td>Corn Marlin</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Lizand ROBERT H </td> <td>Lizand ROBERT </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CATANESE OLGA M</td> <td>CATANESE OLGA </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">JIMENEZ HILDA M</td> <td>JIMENEZ HILDA </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Corn Marlin </td> <td>Corn Marlin </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hernara Marie Anne Martin</td> <td>Hernara Marie </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">White John M </td> <td>White John </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hernara Marie Anne Martin</td> <td>Hernara Marie </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hernara Martin Marie Anne</td> <td>Hernara Martin</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hernara -Martin Marie Anne</td> <td>Hernara -Martin </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HernaraMartin Marie Anne</td> <td>HernaraMartin Marie </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MartinHernara Marie Anne</td> <td>MartinHernara Marie </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Martin Marie Anne Hernara</td> <td>Martin Marie </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CORNWALL ANNA</td> <td>CORNWALL ANNA</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HernaraMartin Marie Anne</td> <td>HernaraMartin Marie </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HernaraMartin Marie</td> <td>HernaraMartin Marie</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Cortes Ann</td> <td>Cortes Ann</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smith Marie Anne</td> <td>Smith Marie </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sarah Ann Little Junior</td> <td>Sarah Ann </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Marie Anne</td> <td>Marie Anne</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HOSTRUP KNUD J </td> <td>HOSTRUP KNUD </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TABAR RAFIK RAYES </td> <td>TABAR RAFIK </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PRIMEAU</td> <td>PRIMEAU</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HELEN BAIN ED </td> <td>HELEN BAIN </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">HELEN BAIN ED </td> <td>HELEN BAIN </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PRIMEAU</td> <td>PRIMEAU</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TABAR RAFIK RAYES </td> <td>TABAR RAFIK </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hernara Martin Marie Anne</td> <td>Hernara Martin</td> </tr> </tbody></table>Thanks for response.

 

[Rasm]

Try to use the 'Split' command with a space as a dilimeter - u will then get an array of your values - then you can decide what is first and last names and so on

So something like this

Dim MyString as string
MyString=Split(Astr," ")

Where Astr=string to be split

 

[Peter_SSs]

John

The right hand column seems (mostly) to be what your formula would produce. Can you show just what results you would want given the left hand data and explain in words why?

 

[johnmerlino]

Thanks for responses. Everything in that right column is exactly how I want it except this name:

Martin Marie Anne Hernara

It currently produces:

Martin Marie

where I want it to produce:

Martin Marie Anne

without compromising the results of the other data.

Thanks for response.

 

[johnmerlino]

Thanks for responses. Everything in that right column is exactly how I want it except this name:

Martin Marie Anne Hernara

It currently produces:

Martin Marie

where I want it to produce:

Martin Marie Anne

without compromising the results of the other data.

Thanks for response.

And this:
Hernara -Martin Marie Anne

produces:
Hernara -Martin

when I want:

Hernara -Martin Marie

without compromising the results of the others.

 

[Peter_SSs]

From what I can understand of your request, you are not going to be able to do this. Any formula has to work on logic, it cannot make up it's own mind what is a last name or first name etc.

You want these four words: Hernara Martin Marie Anne
To result in two words: Hernara Martin

You want these four words: Martin Marie Anne Hernara
To result in three words: Martin Marie Anne

There is no way a formula, or a person, can disninguish the difference unless you have a comprehensive list of last names. Even then it would most likely be impossible because I think you are saying in the second example that the last name is Martin Marie and the first names are Anne Hernara. If so what would stop another identical name in the list being made up of last name Martin with three first names Marie Anne Hernara?

Two other points about your existing formula.

1. It may not be a problem for you, but it is producing an extra blank space at the end of most of the results. For example with "AVERNA ROBERT C" the formula result is "AVERNA ROBERT " not "AVERNA ROBERT"

2. I think you are saying the formula produced the results in the right hand column of post #1. For me it doesn't give that results for the single word value "PRIMEAU". Instead I get #VALUE!

 

[johnmerlino]

From what I can understand of your request, you are not going to be able to do this. Any formula has to work on logic, it cannot make up it's own mind what is a last name or first name etc.

You want these four words: Hernara Martin Marie Anne
To result in two words: Hernara Martin

You want these four words: Martin Marie Anne Hernara
To result in three words: Martin Marie Anne

No, I would want:
Hernara Martin Marie Anne

to result in

Hernara Martin Marie

And

Martin Marie Anne Hernara

to result in:

Martin Marie Anne


Thanks for response.

 

[Peter_SSs]

Sorry, I think I may be getting more confused. Here is your data copied from post #1. I've posted this to show row numbers etc and to highlight a couple of items.

Excel Workbook
	A	B	C
1	AVERNA ROBERT C	AVERNA ROBERT
2	Corn Marlin	Corn Marlin
3	Lizand ROBERT H	Lizand ROBERT
4	CATANESE OLGA M	CATANESE OLGA
5	JIMENEZ HILDA M	JIMENEZ HILDA
6	Corn Marlin	Corn Marlin
7	Hernara Marie Anne Martin	Hernara Marie
8	White John M	White John
9	Hernara Marie Anne Martin	Hernara Marie
10	Hernara Martin Marie Anne	Hernara Martin	Hernara Martin Marie
11	Hernara -Martin Marie Anne	Hernara -Martin	Hernara -Martin Marie
12	HernaraMartin Marie Anne	HernaraMartin Marie
13	MartinHernara Marie Anne	MartinHernara Marie
14	Martin Marie Anne Hernara	Martin Marie	Martin Marie Anne
15	CORNWALL ANNA	CORNWALL ANNA
16	HernaraMartin Marie Anne	HernaraMartin Marie
17	HernaraMartin Marie	HernaraMartin Marie
18	Cortes Ann	Cortes Ann
19	Smith Marie Anne	Smith Marie
20	Sarah Ann Little Junior	Sarah Ann
21	Marie Anne	Marie Anne
22	HOSTRUP KNUD J	HOSTRUP KNUD
23	TABAR RAFIK RAYES	TABAR RAFIK
24	PRIMEAU	PRIMEAU
25	HELEN BAIN ED	HELEN BAIN
26	HELEN BAIN ED	HELEN BAIN
27	PRIMEAU	PRIMEAU
28	TABAR RAFIK RAYES	TABAR RAFIK
29	Hernara Martin Marie Anne	Hernara Martin
Names

Everything in that right column is exactly how I want it except this name:

Martin Marie Anne Hernara

It currently produces:

Martin Marie

where I want it to produce:

Martin Marie Anne

without compromising the results of the other data.

And this:
Hernara -Martin Marie Anne

produces:
Hernara -Martin

when I want:

Hernara -Martin Marie

without compromising the results of the others.

To me, the above two quotes indicate that everything on the right is correct except for rows 11 and 14 (orange rows). I have placed the desired result for these two rows in column C.


Then the following quote seems to be saying that rows 10 and 14 are incorrect. That is row 10 (green) that was previously considered correct is now also considered incorrect. I have placed what you now say you want for row 10 in column C also.

No, I would want:
Hernara Martin Marie Anne

to result in

Hernara Martin Marie

And

Martin Marie Anne Hernara

to result in:

Martin Marie Anne

As I see it, the problem still remains. How would we logically distinguish between, say, rows 9 and 10? They both have four words in column A, but the desired result for row 9 has 2 words and the desired result for row 10 has 3 words.

If you still think this may be possible, I think the best thing would be to re-post your sample data with the (carefully checked) desired results entered on all rows and some explanation, if possible, about the logic behind how to determine those desired results.