Change order of text string

[GScott]

I have a cell that contain the text (say) CITIZEN, JOE MR

What I need to do is write a formula that results in MR JOE CITIZEN in once cell.


Can anyone suggest an appropriate formula please.

thanks

 

[Peter_SSs]

Here is an example

Thank you, that is much clearer. For example, this sample also has a double surname which we hadn't seen before.

Excel Workbook
	A	B
1	CITIZEN HAWKINS, ELIANE MRS	MRS ELIANE CITIZEN HAWKINS
2	CITIZEN, SUSAN MRS	MRS SUSAN CITIZEN
3	JOHNSON, CLINTON MR (CODE RED)	MR CLINTON JOHNSON (CODE RED)
4	DEAKIN, RICHARD MR	MR RICHARD DEAKIN
5	WANDAH, PETRI-SEBASTIAN MR	MR PETRI-SEBASTIAN WANDAH
6	PETERS-JONES, SUZANNE MS	MS SUZANNE PETERS-JONES
7	JIMBO, PER MR (YELLOW)	MR PER JIMBO (YELLOW)
8	LYNCH-WATKINSON, KATIER MS	MS KATIER LYNCH-WATKINSON
9	LIMBO, LI MEI MRS (CODE WHITE-ORANGE)	MRS LI MEI LIMBO (CODE WHITE-ORANGE)
Rearrange Names

 

[bosco_yip]

Another option :

=LOOKUP(2,1/FIND({"MR","MRS","MS"},A1),{"MR","MRS","MS"})&MID(A1,FIND(",",A1)+1,MIN(FIND({"MR","MRS","MS"},A1&"MRMRSMS")-FIND(",",A1)-1))&LEFT(A1,FIND(",",A1)-1)&MID(A1,FIND(" (",A1&" ("),99)

Regards

 

[GScott]

Thanks, these are great formulas.

 

[Peter_SSs]

Another option :

=LOOKUP(2,1/FIND({"MR","MRS","MS"},A1),{"MR","MRS","MS"})&MID(A1,FIND(",",A1)+1,MIN(FIND({"MR","MRS","MS"},A1&"MRMRSMS")-FIND(",",A1)-1))&LEFT(A1,FIND(",",A1)-1)&MID(A1,FIND(" (",A1&" ("),99)

Regards

That will fail or produce erroneous results for names like

THOMSON, ELIANE MRS
CITIZEN, KAMRAN MR

 

[bosco_yip]

That will fail or produce erroneous results for names like

THOMSON, ELIANE MRS
CITIZEN, KAMRAN MR

Then......

In avoid the words contain MR, MRS and MS, herein the following formula is fixed by adding a space behind :

=LOOKUP(2,1/FIND({" MR"," MRS"," MS"},A1),{"MR","MRS","MS"})&MID(A1,FIND(",",A1)+1,MIN(FIND({" MR"," MRS"," MS"},A1&" MR MRS MS")-FIND(",",A1)))&LEFT(A1,FIND(",",A1)-1)&MID(A1,FIND(" (",A1&" ("),99)

Regards

 

[Peter_SSs]

Then......

In avoid the words contain MR, MRS and MS, ..

Then we just have to hope there are no titles like MISS, BR, FR, HON etc.

 

[GScott]

I had identified the issues you mentioned.
Peter's solution is the preferable one because it avoids the need to define the salutations. Whilst I don't have any DR's or PROF's much less MISS or HON, it is possible somebody might try and enter this information.

Thanks for your efforts. I know have a solution.

Gavin

 

[Peter_SSs]

.. much less MISS ..

That's interesting. I don't know where your list relates to but Miss would be a very common title here in Australia.

In any case, glad you have something that you can work with.