Right function, Left function, Mid function to extract values in column A

[bjnockle]

Looking for formula (Right function, Left function, or Mid function) to extract values in column A and place initials in column B, middle name in column C and firstname in column D. See attached sample file. Thanks.

First Logic
Names without initial should be placed in column D

Second Logic
Names with initial:
1. Place initial in column B (e.g. John A. - A. will be in column B)
2. Place name in column D (e.g. John A. - John will be in column D)

Third Logic
Names with middle name:
1. Place middle name in column C (e.g. Afsaneh Mashayekhi. - Mashayekhi will be in column C)
2. Place name in column D (e.g. Afsaneh Mashayekhi. - Afsaneh will be in column D)

Fourth Logic
Initial and name in paranthesis:
1. Place initial in column A (e.g. A. C. (Del). - A. C. will be in column A)
2. Place name in column D (e.g. A. C. (Del). - (Del) will be in column D)

Names in this format:
Name: Bin-Ming (Benjamin) - means no middle name or initial
Name: Catherine E. (Kate) - initial is E. and first name is Catherine (Kate) - kate in parathesis is alias for Catherine so we want to have it as Catherine (Kate) (same for this type of name Edward E. "Ned")

[AliGW]

I can see a big problem with your requirement, and this is the way you have dealt with entries starting at row 6 - what is the logic you have used that means that the initial here is seen as the middle initial and not the initial of the first name? Unless you can define your logic, we can't replicate it in Excel. Excel is not intelligent to learn human judgement, so can you explain the rule you've used in this sample data?

Rows 29 and 33 - how would Excel be able to tell any difference her?

[bjnockle]

AliGW: I can see a big problem with your requirement, and this is the way you have dealt with entries starting at row 6 - answer: meant to say "initial" and not middle initial. Thanks for the catch.

See updated sample file.

[AliGW]

There are still several anomalies, though. We need some logic - where there are no initials, how should Excel decide where to place the names? Where there are double initials, how should it know whether to treat it as a first name or not?

Again, we need to know the rules.

Tell us the thought processes you apply when doing this manually.

[bjnockle]

AliGW: I have revised the file based on your feedback for clearer requirements.
Note: should be treated as first name in the dataset.
A.B.
A.H.
A.H

[AliGW]

Just to be clear, there are too many unexplained patterns at the moment for me to start helping - I'd have to guess in a lot of cases, and I'd probably guess wrong.

[AliGW]

OK - let me have another look.

[AliGW]

OK - sorry, but there are still more questions than answers!

Please explain your logic for these rows:

Excel 2016 (Windows) 32 bit

	A	B	C	D
4	John A.	A.		John
5	A	A
6	A. Alexander	A.		Alexander
7	A. Alexander (Sandy)	A.		Alexander (Sandy)
8	A. C. (Del)			A. C. (Del)

Sheet: Sheet1 (3)

Got to go and plant out some runner beans. Back later.

[bjnockle]

AliGW: updated file. Sorry for the inconvenience. See sample file.

[AliGW]

OK - I'm sorry, but I don't have time for this.

You are expecting ME to go right the way through your dataset and work out the rules that YOU are already applying! NOWHERE have you yet told me what these are, despite my asking now at least three times. I don't have time to back-engineer it all, and I shouldn't even have to be doing this.

All I wanted you to do is EXPLAIN the logic YOU are applying to decide on where things should go so that I can replicate that in Excel. Now you've added a third results column, but still no explanation. If you can't be bothered to list what those rules (your logic) are, then I'm sorry, but I can't be bothered to try to help you find a solution.

I'm out.

[bjnockle]

AliGW: I am writing/explaining the logic. Please standby. Thanks

First Logic
Names without initial should be placed in column D

Second Logic
Names with initial:
1. Place initial in column B (e.g. John A. - A. will be in column B)
2. Place name in column D (e.g. John A. - John will be in column D)

Third Logic
Names with middle name:
1. Place middle name in column C (e.g. Afsaneh Mashayekhi. - Mashayekhi will be in column C)
2. Place name in column D (e.g. Afsaneh Mashayekhi. - Afsaneh will be in column D)

Fourth Logic
Initial and name in paranthesis:
1. Place initial in column A (e.g. A. C. (Del). - A. C. will be in column A)
2. Place name in column D (e.g. A. C. (Del). - (Del) will be in column D)

Names in this format:
Name: Bin-Ming (Benjamin) - means no middle name or initial
Name: Catherine E. (Kate) - initial is E. and first name is Catherine (Kate) - kate in parathesis is alias for Catherine so we want to have it as Catherine (Kate)

[AliGW]

No, sorry - you have had your window of opportunity with me. Hopefully someone else will step in. It should not have taken this long for you to provide what I've been asking for since post #2 (THREE hours ago)!

[bjnockle]

Okay. I understand your frustration. Thanks.

[AliGW]

You still need to provide that information for anyone else who wants to help.

EDIT: I see you you’ve added it to the opening post and lower down.

[Bo_Ry]

Please try at
B2

=IF(LEN(A2)=1,"",IFERROR(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),AGGREGATE(15,6,{1,51,101,151}/(MMULT({1,1},--(--ISNUMBER(FIND({".";"("},TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),{1,51,101,151},49))&REPT(".",LEN(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),{1,51,101,151},49)))=1)))={1;0}))=2),1),SUMPRODUCT(--(MMULT({1,1},--(--ISNUMBER(FIND({".";"("},TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),{1,51,101,151},49))&REPT(".",LEN(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),{1,51,101,151},49)))=1)))={1;0}))=2))*50)),""))

C2

=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A2,B2,,1))," ",REPT(" ",50)),(MATCH(FALSE,INDEX(ISNUMBER(FIND("(",MID(SUBSTITUTE(TRIM(SUBSTITUTE(A2,B2,,1))," ",REPT(" ",50)),{51,101},50))),),))*50+1,50)),"")

D2

=LEFT(TRIM(SUBSTITUTE(A2,B2,,1)),FIND(" ",TRIM(SUBSTITUTE(A2,B2,,1))&" "))&IFERROR(" "&TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A2,B2,,1))," ",REPT(" ",50)),(MATCH(TRUE,INDEX(ISNUMBER(FIND("(",MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"""","("),B2,,1))," ",REPT(" ",50)),{51,101},50))),),))*50+1,50)),"")

[bjnockle]

Bo_Ry:
Some of it are not showing the correct results. See wrong sample below.

Yen should not be a middle name.

Chung Yen Yen Chung
Chung Yen (Tom) Yen Chung (Tom)

The below names should not fall under initial.

Dr. Albert
Dr.
Dr. Charles
Dr. E.
Dr.
Dr. John
Dr. Lynne
Dr. Malcolm
Dr.
Dr. R.
Dr. Ralph
Dr. Randolph
Dr. W.
Dr.A.
Dr.Aart J.
Dr.Chi-Foon
Dr.Eli
Dr.Harry R.
Dr.Michael J.
Dr.Sasson

[Bo_Ry]

Sorry, I have no idea how to put Fox to the middle name without Yen stay with the First name.

Brian Fox
Chung Yen
Chung Yen (Tom)

The Dr. part is possible, I will try tomorrow.

[RaulSerg]

From a database perspective, this is pretty much a case of: Garbage in, garbage out.

- Anthony Dr. vs. Dr. Albert E.
- Dr.Michael J. (missing space)
- Julius W. "Dr. J"

What exactly is the name pattern/logic here:
First Name (Standardize) - Middle Name - First Name
Chung Yen - - Chung Yen
Chung Yen (Tom) - - Chung Yen (Tom)
Tom Chung Yen - Chung Yen - Tom

I highly doubt that formulas will help you here, as you need to make exceptions for apparently wrong/inconsistent database inputs.
And the next time you make an addition that is another style of wrong input, formulas won't work again.

I'd suggest you clean up the data first.

[Bo_Ry]

I can't get all the expected result, This is just for fun.

B2

=IF(LEN(A2)=1,"",IFERROR(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),AGGREGATE(15,6,{1,51,101,151}/(MMULT({1,1},--(--ISNUMBER(FIND({".";"("},SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),{1,51,101,151},49))&REPT(".",LEN(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),{1,51,101,151},49)))=1),"Dr.","@")))={1;0}))=2),1),SUMPRODUCT(--(MMULT({1,1},--(--ISNUMBER(FIND({".";"("},SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),{1,51,101,151},49))&REPT(".",LEN(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),{1,51,101,151},49)))=1),"Dr.","@")))={1;0}))=2))*50)),""))

C2
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,B2,),LEFT(D2,FIND(" ",D2&" ")-1),),MID(D2,FIND(" ",D2&" ")+1,20),))

D2

=SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"Dr. ","@"),B2,,1)),FIND(" ",TRIM(SUBSTITUTE(A2,B2,,1))&" ")-1)&IFERROR(" "&TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"Dr. ","@"),B2,,1))," ",REPT(" ",50)),(MATCH(TRUE,INDEX(ISNUMBER(FIND("(",MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Dr. ","@"),"""","("),B2,,1))," ",REPT(" ",50)),{51,101},50))),),))*50+1,50)),""),"@","Dr. ")