How to Fix Problem - Formulas for extracting names?

Formulas for extracting names

Hi All,
I need formulas to extract names.
Below are the examples.
I used the below formulas, but do not return the expected outputs for some names. Please help with some modifications, or any other formulas that may meet the requirements. 
Also, the names preceding with "Mohd.", "Mohammed", "Mohammad", or "Mohd" should return with surname or middle name as mentioned in the table.
First Name
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,CHAR(32),REPT(CHAR(32),99)),CHAR(45),REPT(CHAR(32),99)),CHAR(47),REPT(CHAR(32),99)),99))
Surname
=IFERROR(IF(LEN(D2)-FIND(" ",D2)<>1,RIGHT(D2,LEN(D2)-FIND(" ",D2)),D2),"")


Name
First Name
Surname

Richelda Tay Sy
Richelda
Tay Sy

Mohd Zeeshan
Mohd Zeeshan

Mohdammed Zeeshan
Mohdammed Zeeshan

Mohd.
Zeeshan
Mohd.
Zeeshan

P Conanan
P Conanan

S V Raymund
S V
Raymund

Mohammad Ahmed Hadeya Yaun Fhamy
Mohammad Ahmed
Hadeya Yaun Fhamy

Mukesh Gangaram Salve
Mukesh
Gangaram Salve

Manjunatha S B
Manjunatha
S B

S Navneeth Prasad
S Navneeth
Prasad

D Coutinho H
D Coutinho
H

Anju S
Anju S

Luke S R
Luke S
R

Anuradha Mishra
Anuradha
Mishra


Please assist..
Thanks in advance

Anwsers to the Problem Formulas for extracting names

Download SmartPCFixer to Fix It (Free)

Hans V.
and I have seemed to switch rolls!!  Usually I come up with a VBA solution and Hans handles the formula...
But
These formulas work for all of your test cases so far:
In E2:
=TRIM(IF(LEFT(D2,5)="mohd ",LEFT(D2 & " ",FIND(" ",D2 & " ",6)),IF(LEFT(D2,6)="mohd.
",LEFT(D2 & " ",FIND(" ",D2 & " ",7)),IF(LEFT(D2,9)="mohammad ",LEFT(D2 & " ",FIND(" ",D2 & " ",10)),IF(LEFT(D2,9)="mohammed ",LEFT(D2 & " ",FIND(" ",D2 & " ",10)),IF(LEN(RIGHT(D2,4))-LEN(SUBSTITUTE(RIGHT(D2,4),"
",""))=2,LEFT(D2,LEN(D2)-4),IF(4-LEN(SUBSTITUTE(LEFT(D2,4)," ",""))=2,LEFT(D2,4),IF(IFERROR(FIND(" ",D2),0)=2,LEFT(D2&" ",FIND(" ",D2&" ",3)),LEFT(D2 & " ",FIND(" ",D2 & " ",1))))))))))
In F2 for the Surnames:
=TRIM(RIGHT(D2,LEN(D2)-LEN(E2)))
One thing to note:  if there is a chance of the workbook ever being used in a version of Excel earlier than Excel 2007 - definitely go with the VBA solution!!  The level of nested functions in the formula in column E is too many for Excel 2003 and earlier
versions to process without error.

Open Action Center by clicking the Start button Picture of the Start button, clicking Control Panel, and then, under System and Security, clicking Review your computer's status.

Make sure your computer has at least the minimum amount of random access memory (RAM) required to run the program that is displaying the error. Look for the program requirements online or in the packaging information.

Another Safe way to Fix the Problem: Formulas for extracting names:

 

 

How to Fix Formulas for extracting names with SmartPCFixer?

1. Download SmartPCFixer . Install it on your system.  Click Scan, and it will perform a scan for your computer. The errors will be shown in the scan result.

2. After the scan is done, you can see the errors and problems need to be repaired. Click Fix All.

3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.

Related: Action Center On My Computer Stopped Working About A Week Ago. It Won't Open.,"Atheros AR9485WB-EG Wireless Network Adaptor Is Experiencing Driver Or Hardware-related Problems" How Do I Fix This?,4 Questions On Restore Files And Recover System,Allowings One Person To Access A MS Access Database At A Time,Arrow Cursor On Desktop Screen,0 Is Showing As A Result Of Formula,Win 7 Backup And Restore Program Content Question,Windows 7 Will Only Start In Safe Mode!,Windows Was Unable To Create A Required Instillation Folder Error 0x80070005,Presentation Mode Is Always On When I Turn On My Computer. My Desktop Icons Are Gone And The Computer Seems To Run Slower.,Title Of Saved File,How Can I Change My E-mail From Windows Live Mail To Yahoo Mail? Whenever I Try And Send Something In Windows Mail It Tells Me It's The Wrong Server.,How To Merge/delete Networks In Windows 8/8.1 ? The Option Is Missing Here.,When I Use Hibernate Mode For More Than 2 Days I Can't Load Windows 7 Home Premium,Display Driver Has Stopped Working And Has Recovered Error
Read More: [Solution] Unable to connect to web host.,How do you prevent empty text form fields from printing on a template? [Anwsered],[Solved] How to fix my cursor that jumps around while I typing. I have a Dell Inspriron and windows 7.,AMD Radeon HD 7400 graphic card - HP pavilion G4 1200tx models graphic driver not working properly after upgrading windows 8 [Solved],[Solution] why won't zygna open on internet explorer,Pc does not meet security requirements when trying to connect the computer remotely,IE 11 options under Security -- Internet Zone is locked,matlab 2007 windows 7 incompatibility,Windows 7 compatibility not working,homegroup setup fails