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

 

[Special-K99]

There's probably a neater way of doing this but this works.

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",500)),500))&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",500)),250,500))&" "&SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",500)),500)),",","")

You should really provide more example data than one instance

 

[GScott]

Thanks, your formula is good but you are right - O do need to provide more sample data. One example I overlooked was that some names are 3 words long. For example CITIZEN, JOE CANE MR or CITIZEN, JOE-CANE MR

which I need to read MR JOE CANE CITIZEN

Can a formula be written for that situation as well as the case when there are ony two names in a person's name?

thanks

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B
1	CITIZEN, JOE MR	MR JOE CITIZEN
2	CITIZEN, JOE CANE MR	MR JOE CANE CITIZEN
3	CITIZEN, JOE-CANE MR	MR JOE-CANE CITIZEN
4	CITIZEN, JOE CANE BOB FREDERICK MR	MR JOE CANE BOB FREDERICK CITIZEN
Names

 

[GScott]

Thanks Peter, that is great. Phew, I was convinced this couldn't be done.

thanks again,

gavin

 

[Peter_SSs]

Glad it helped.

 

[GScott]

One more question...

lets say the cell contains the text CITIZEN, JOE CANE Mr (code red)

can this be read so that it is comes out as
Mr JOE CANE CITIZEN (code red)

thanks for any help

gavin

 

[Peter_SSs]

lets say the cell contains the text CITIZEN, JOE CANE Mr (code red)

can this be read so that it is comes out as
Mr JOE CANE CITIZEN (code red)

As pointed out earlier, one example isn't much to go on.

Do all rows have something in parentheses at the end or only some?

Could we have 5-10 samples & their results that show all the different cases that any formula would have to deal with?

 

[GScott]

Thanks Peter,

Here is an example

[TABLE="width: 614"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]CITIZEN HAWKINS, ELIANE MRS[/TD]
[TD]MRS ELIANE CITIZEN HAWKINS[/TD]
[/TR]
[TR]
[TD]CITIZEN, SUSAN MRS[/TD]
[TD]MRS SUSAN CITIZEN[/TD]
[/TR]
[TR]
[TD]JOHNSON, CLINTON MR (CODE RED)[/TD]
[TD]MR CLINTON JOHNSON (CODE RED)[/TD]
[/TR]
[TR]
[TD]DEAKIN, RICHARD MR[/TD]
[TD]MR RICHARD DEAKIN[/TD]
[/TR]
[TR]
[TD]WANDAH, PETRI-SEBASTIAN MR[/TD]
[TD]MR PETRI-SEBASTIAN WANDAH[/TD]
[/TR]
[TR]
[TD]PETERS-JONES, SUZANNE MS[/TD]
[TD]MS SUZANNE PETERS-JONES[/TD]
[/TR]
[TR]
[TD]JIMBO, PER MR (YELLOW)[/TD]
[TD]MR PER JIMBO (YELLOW)[/TD]
[/TR]
[TR]
[TD]LYNCH-WATKINSON, KATIER MS[/TD]
[TD]MS KATIER LYNCH-WATKINSON[/TD]
[/TR]
[TR]
[TD]LIMBO, LI MEI MRS (CODE WHITE-ORANGE)[/TD]
[TD]MRS LI MEI LIMBO (CODE WHITE-ORANGE)[/TD]
[/TR]
</tbody>[/TABLE]

 

[GScott]

I should have added the column on the left is the raw data, column on the right is how I would like it to be translated.