Long 'IF' formula troubles

[itstombenson]

Hi all,

I have a dataset of people's names, where their full name (e.g Mr. John Smith) is in one column and the surname is in the next. I would like to create a formula that allows me to split out their salutation (e.g Mr, Mrs, Mr and Mrs, Herr, Frau etc) and list it as a single variable in another column.

I tried to adapt an 'IF' formula I found in another thread but Excel said I had too many rules. Does anyone know of a way that I can do this?

Thanks

[JohnTopley]

It will help if you can post a file with a good set of sample data so we can see the variability in the data.

To upload a file, click "Go Advanced" then "Manage attachments"

[FDibbins]

Agree a sample file will help, but assuming your data looks something like this...

	A	B	C	D
1	Mr aa bb	Mr	aa	bb
2	Mr. aa bb	Mr	aa	bb
3	Mrs aa bb	Mrs	aa	bb
4	Mrs. aa bb	Mrs	aa	bb

B1=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*(COLUMN()-2)+1,LEN($A1))),".","")
copied down and across as needed

[itstombenson]

I'm sorry guys, but I can't share the data file as it contains people's personal information. It looks a bit like below though. Ideally I would like column C to house the salutation.

	A	B
	Full Name	Surname
1	Mr John Jones	Jones
2	Mrs Sam Smith	Smith
3	Mr and Mrs Steve Doe	Doe
4	Herr Klaus Schmid	Schmid
5	Frau Laura Weber	Weber