Error trapping

ArviLaanemets · #2 12-18-2017, 11:50 PM

A correct format will be like:
Forename, Surname, Title
John, Smith, Mr

To determine, which part in full name belongs to where is possible only, when there are certain rules how this full name is composed, and the number of those rules is limited, as you have to include all of them into every formula.

P.e.
1 When the full name doesn't have comma in it, then 1st part is may be title with possible values Mr/Ms/Mrs;
2.a. When the full name doesn't have comma in it, then 1st part after title is forename, and the rest is surname;
or
2.b. When the full name doesn't have comma in it, then last part is surname, and the rest except title is forename;
3. Otherwise at end of full name may be title. All before comma is surname, and all after comma and space except title is forname;

When these rules are always fulfilled, you can calculate all parts of name. To keep formulas length reasonable, I advice to use helper columns which you can hide later.
Title:

Code:

 
=IF(FIND(",",FullName)>0,
IF(RIGHT(FullName,3)=" Mr","Mr", IF(RIGHT(FullName,3)=" Ms","Ms",IF(RIGHT(FullName,4)=" Mrs","Mrs",""))),
IF(LEFT(FullName,3)="Mr ","Mr", IF(LEFT(FullName,3)="Ms ","Ms",IF(LEFT(FullName,4)="Mrs ","Mrs",""))))

NameWithoutTitle

Code:

 
=IF(FIND(",",FullName)>0,
IF(RIGHT(FullName,3)=" Mr", LEFT(FullName,LEN(FullName)-3), IF(RIGHT(FullName,3)=" Ms",LEFT(FullName,LEN(FullName)-3),IF(RIGHT(FullName,4)=" Mrs", LEFT(FullName,LEN(FullName)-4),FullName))),
IF(LEFT(FullName,3)="Mr ",RIGHT(FullName,LEN(FullName)-3), IF(LEFT(FullName,3)="Ms ",RIGHT(FullName,LEN(FullName)-3),IF(LEFT(FullName,4)="Mrs ",RIGHT(FullName,LEN(FullName)-4),FullName))))

ForeName:

Code:

 
=IF(FIND(",",FullName)>0,
RIGHT(NameWithoutTitle, LEN(NameWithoutTitle)-FIND(" ", NameWithoutTitle)),
LEFT(NameWithoutTitle, FIND(" ", NameWithoutTitle) -1)

etc.

PS. You have to check formulas, as I did write them on fly.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automation error Unknown error" message once they open the Excel file	hlina	Excel	1	10-08-2013 09:14 PM
Word Visual Basic error - run time error 504	crazymorton	Word	11	01-13-2012 04:32 AM
Microsoft office 2010 error 2908 and error 1935 !!!!!!heeeeellpppp!!!!!!!!!	bennypryde	Office	1	01-05-2012 03:33 PM
Runtime error 5487 - Word cannot complete the save to to file permission error	franferns	Word	0	11-25-2009 05:35 AM
Receive error cannot open this form because an error occurred in BCM 2007	bornhusker	Outlook	0	06-01-2009 10:28 AM