Switching Surname, Initial, Title to other way round

[Raving12]

Hi there

I have a column where the names are like "Armstrong C M Mrs" and others "Armstrong A P Mr & Mrs". I need to show these names as "Mrs C M Armstrong" and "Mr & Mrs A P Armstrong"

Can anyone help with a formula that can do this? Getting tied up in knots!

Thanks!

 

[wideboydixon]

I'm making some assumptions here and I'm guessing your data isn't as nice as you're making out but for the specific cases you've given, the following would work:

Book1
	A	B
1	Amstrong A P Mr & Mrs	Mr & Mrs A P Amstrong
2	Armstrong C M Mrs	Mrs C M Armstrong
Sheet1

Cell Formulas
Range	Formula
B1	=MID($A1,FIND("Mr",$A1),LEN($A1))&" "&MID($A1,FIND(" ",$A1)+1,FIND("Mr",$A1)-FIND(" ",$A1)-1)&LEFT($A1,FIND(" ",$A1)-1)

WBD

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm guessing that you have quite a bit of variety in your data format. For example ..

- Do some people have less or more than 2 initials? (eg Armstrong C Mrs or Armstrong A P B Mr & Mrs)
- Do some people have multi-word last names? (eg van den Berg A B Mr or del Potro J M Mr)
- Are there other titles? (eg Armstrong R B Dr)
- Are there any suffixes? (eg Armstron Jr W K Mr)
- Do some people have one or more of their first names written out in full? (eg Armstrong Carol Megan Miss or Smith Tom T Prof)
- etc

If any of these are possible, please give us a better representative sample data set and the expected results.