Revers name

[sksanjeev786]

Hi Team,

I need a name in reverse formate as of now I can able to do with 2 words but while trying with 3 words not getting correct solution

Output : Verma Kumar Raj

Excel.xls
	F	G
62	Raj Kumar Verma	Kumar Verma Raj
Test (3)

Cell Formulas
Range	Formula
G62	G62	=MID(F62&" "&F62,FIND(" ",F62)+1,LEN(F62))

 

[lrobbo314]

Just for fun, a Power Query way of doing it.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Reverse = Table.TransformColumns(Source,{{"Column1", each Text.Combine(List.Reverse(Text.Split(_, " "))," ")}})
in
    Reverse

Unwind with formulas and VBA.xlsm
	G	H
1	Column1	Column1
2	Raj Kumar Verma	Verma Kumar Raj
Sheet2

 

[Peter_SSs]

Yet more formula options.

B1 if you have the TEXTSPLIT function in your 365 version.
B2 if not (allows for up to 9 names)

22 05 03.xlsm
	A	B
1	Raj Kumar Verma	Verma Kumar Raj
2	Raj Kumar Verma	Verma Kumar Raj
Reverse

Cell Formulas
Range	Formula
B1	B1	=LET(ts,TEXTSPLIT(A1," "),TEXTJOIN(" ",,SORTBY(ts,SEQUENCE(,COLUMNS(ts)),-1)))
B2	B2	=TRIM(CONCAT(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",100)),100*SEQUENCE(9)),100)))

 

[Phuoc]

Another way:

Book1
	A	B
1	Raj Kumar Verma	Verma Raj Kumar
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))&" "&A1, LEN(A1))

 

[shaowu459]

More options for office 365:

工作簿1
	A	B	C
1	Raj Kumar Verma	Verma Raj Kumar
2		Verma Raj Kumar
3		Verma Raj Kumar
4
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTAFTER(TEXTBEFORE(A1&" "&A1," ",5)," ",2)
B2	B2	=TEXTJOIN(" ",,CHOOSECOLS(TEXTSPLIT(A1," "),{3,1,2}))
B3	B3	=TEXTAFTER(A1," ",2)&" "&TEXTBEFORE(A1," ",2)

 

[Peter_SSs]

@Phuoc @shaowu459
I don't believe any of those formulas produce the result that the OP is looking for

Output : Verma Kumar Raj

The result shown in the mini-sheet in post 1 ..

while trying with 3 words not getting correct solution

Also, another UDF (only works up to 9 words but I' assuming that should be enough to reverse a name)

Function RevWords(s As String) As String
  RevWords = Join(Application.Index(Split(s), 0, Split(StrReverse(Join(Application.Transpose(Evaluate("row(1:" & UBound(Split(s)) + 1 & ")")))))))
End Function

22 05 03.xlsm
	A					F
1	Raj Kumar Verma					Verma Kumar Raj
Reverse

Cell Formulas
Range	Formula
F1	F1	=RevWords(A1)

 

[shaowu459]

@Phuoc @shaowu459
I don't believe any of those formulas produce the result that the OP is looking for


The result shown in the mini-sheet in post 1 ..

Oh, sorry. I copied data from post #13

 

[Peter_SSs]

Oh, sorry. I copied data from post #13

Easy enough to do that sort of thing.

Your idea of using CHOOSECOLS though (I'm still not very used to the new functions) does provide a shortening of one of my earlier suggestions.
Here assuming less than 100 words.

22 05 03.xlsm
	A	B
1	Raj Kumar Verma	Verma Kumar Raj
Reverse

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(" ",,CHOOSECOLS(TEXTSPLIT(A1," "),SEQUENCE(,99,99,-1)))

 

[sksanjeev786]

T

@Phuoc @shaowu459
I don't believe any of those formulas produce the result that the OP is looking for


The result shown in the mini-sheet in post 1 ..



Also, another UDF (only works up to 9 words but I' assuming that should be enough to reverse a name)

Function RevWords(s As String) As String
  RevWords = Join(Application.Index(Split(s), 0, Split(StrReverse(Join(Application.Transpose(Evaluate("row(1:" & UBound(Split(s)) + 1 & ")")))))))
End Function

22 05 03.xlsm
	A					F
1	Raj Kumar Verma					Verma Kumar Raj
Reverse

Cell Formulas
Range	Formula
F1	F1	=RevWords(A1)

Thank you so much peter for your help on this?

 

[sksanjeev786]

Easy enough to do that sort of thing.

Your idea of using CHOOSECOLS though (I'm still not very used to the new functions) does provide a shortening of one of my earlier suggestions.
Here assuming less than 100 words.

22 05 03.xlsm
	A	B
1	Raj Kumar Verma	Verma Kumar Raj
Reverse

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(" ",,CHOOSECOLS(TEXTSPLIT(A1," "),SEQUENCE(,99,99,-1)))

Wow!!! With minimum function… super cool…