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

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")

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?

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.

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.

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.

OK - let me have another look.

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.

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.

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)

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)!

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.

Please try at
B2

Formula:

Please Login or Register  to view this content.

C2

Formula:

Please Login or Register  to view this content.

D2

Formula:

Please Login or Register  to view this content.

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.

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

B2

Formula:

Please Login or Register  to view this content.

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

D2

Formula:

Please Login or Register  to view this content.