Help with Formula to arrange words order in cell.

[Sebastiandiaz]

Hello, I am trying to find out the way to arrange the word order in a cell in excel. I managed to get some word changing with a formula, but it still does not do what I need. As you can see, in cell A1, there is a name "Sonnervig,Luiza,Ms.".

I got this formula: =IFERROR(TRIM(RIGHT(A1,LEN(A1)-SEARCH(",",A1))&" "&LEFT(A1,SEARCH(",",A1)-1)),"").

The result of the formula is "Luiza,Ms. Sonnervig".
The result that I need is "Ms. Luiza Sonnervig".
I have tried different ways but for some reason, I can´t fathom, the result is so far away from my little knowledge!

Is there a way in order to get what I want?

I am attaching the file I am using.
Once again, thank you so much in advance.

 

[shrivallabha]

If you have access to TEXTJOIN and FILTERXML functions as well as if your data is as consistent as in the sample then you can try below formula.

=TEXTJOIN(" ",TRUE,FILTERXML("<t><d>"&SUBSTITUTE(A1,",","</d><d>")&"</d></t>","//d["&{3,2,1}&"]"))

 

[Sebastiandiaz]

MMMM I tried and unfortunately I do not have access to TEXTJOIN and FILTERXML functions

 

[AlanSidman]

With Power Query, a case of simply splitting data on delimiter "comma" and then concatenating with a space.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Column1.3", "Column1.2", "Column1.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

[shrivallabha]

MMMM I tried and unfortunately I do not have access to TEXTJOIN and FILTERXML functions

In that case you can use:
=TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),3*99,99))&" "&TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),2*99,99))&" "&TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),1*99,99))

It looks longer but it is just the same formula i.e. TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),3*99,99)) written 3 times where red marked number is changed as per position requirement and concatenated with spaces.

 

[bosco_yip]

Or,

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

Regards

 

[Excel Wizard]

Please try

=TRIM(MID(A1,FIND(",",A1,FIND(",",A1)+1)+1,9)&MID(SUBSTITUTE(A1,",",REPT(" ",99)),99,99)&LEFT(A1,FIND(",",A1)-1))

 

[Sebastiandiaz]

This one worked amazing. Thank you so much.
And I modified it a little like this:
=IFERROR(TRIM(MID(A1,FIND(",",A1,FIND(",",A1)+1)+1,9)&MID(SUBSTITUTE(A1,",",REPT(" ",99)),99,99)&LEFT(A1,FIND(",",A1)-1)),"")

THANK YOU SO MUCH.