Avril,
Before trying the code on the Delegate field I had a go at Conditional formatting another field and then copy / pasting from an emailed spreadsheet.
The formatting did not work but it did when I typed into the cells.
Is there some bizarre format to data in an emailed document?
Ric
Quote:
Originally Posted by ArviLaanemets
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. |
Avril, one thing I tried on the text data fields
|