Reverse name in excel with upper case

[shrinivasmj]

hi,

i need to reverse name excel data in reverse format with upper case as shown below.

INPUT
[TABLE="class: cms_table, width: 280"]
<tbody>[TR]
[TD]anil kapoor singh[/TD]
[/TR]
[TR]
[TD]kiran kumar sen gee[/TD]
[/TR]
[TR]
[TD]super kumar sing meer natak t.
[/TD]
[/TR]
</tbody>[/TABLE]


OUT PUT
[TABLE="class: cms_table, width: 245"]
<tbody>[TR]
[TD]SINGH,ANIL KAPOOR[/TD]
[/TR]
[TR]
[TD]SEN GEE,KIRAN KUMAR
[/TD]
[/TR]
[TR]
[TD]MEER NATAK T,SUPER KUMAR SING
[/TD]
[/TR]
</tbody>[/TABLE]


INSTRUCTION TO DO

[TABLE="class: cms_table, width: 287"]
<tbody>[TR]
[TD]up to-3 words last letter with comma[/TD]
[/TR]
[TR]
[TD]4 to 5 words last 2 letters with comma
[/TD]
[/TR]
[TR]
[TD]>6 words last 3 words with comma
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD]regards
srinivasmj[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Give this formula a try...

=UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),(1+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>2)+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>4))*99))&", "&A1,LEN(A1)+1))

 

[p45cal]

…or this user-defined function:

Excel Workbook
	A	B
11	INPUT	OUT PUT
12	anil kapoor singh	SINGH,ANIL KAPOOR
13	kiran kumar sen gee	SEN GEE,KIRAN KUMAR
14	super kumar sing meer natak t.	MEER NATAK T.,SUPER KUMAR SING
15	anil kapoor	KAPOOR,ANIL
16	anil	ANIL
Sheet

supported by the function's code in a standard codemodule:

Function ReverseMe(OrigStr)
xx = Split(Application.Trim(OrigStr))
Select Case UBound(xx)
    Case 0: Locn = 0
    Case 1 To 2: Locn = UBound(xx)
    Case 3 To 4: Locn = UBound(xx) - 1
    Case Is > 4: Locn = UBound(xx) - 2
End Select
For i = Locn To UBound(xx)
    PartOne = PartOne & " " & xx(i)
Next i
For i = 0 To Locn - 1
    PartTwo = PartTwo & " " & xx(i)
Next i
ReverseMe = UCase(Application.Trim(PartOne) & IIf(Locn > 0, ",", "") & Application.Trim(PartTwo))
End Function

 

[p45cal]

deleted (duplicate post)

 

[Rick Rothstein]

…or this user-defined function:

Function ReverseMe(OrigStr)
xx = Split(Application.Trim(OrigStr))
Select Case UBound(xx)
    Case 0: Locn = 0
    Case 1 To 2: Locn = UBound(xx)
    Case 3 To 4: Locn = UBound(xx) - 1
    Case Is > 4: Locn = UBound(xx) - 2
End Select
For i = Locn To UBound(xx)
    PartOne = PartOne & " " & xx(i)
Next i
For i = 0 To Locn - 1
    PartTwo = PartTwo & " " & xx(i)
Next i
ReverseMe = UCase(Application.Trim(PartOne) & IIf(Locn > 0, ",", "") & Application.Trim(PartTwo))
End Function

Here is a slightly shorter UDF that also works...

Function ReverseNames(S As String) As String
  Dim Spaces As Long, Parts() As String
  Spaces = UBound(Split(S))
  Parts = Split(Application.Substitute(S, " ", "|", Spaces - (Spaces = 0) + (Spaces > 2) + (Spaces > 4)) & "|", "|")
  ReverseNames = Mid(UCase(Parts(1) & ", " & Parts(0)), 1 - 2 * (Spaces = 0))
End Function

 

[shrinivasmj]

Give this formula a try...

=UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),(1+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>2)+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>4))*99))&", "&A1,LEN(A1)+1))

thanks a lot you made my day
need some changes
i need if there is more then 6 words it should take last 3 words

it will be great help

Thanks a lot

 

[Rick Rothstein]

thanks a lot you made my day
need some changes
i need if there is more then 6 words it should take last 3 words

Unless I am not understanding your request correctly, the formula I posted already does that. If this is in the cell...

one two three four five six seven eight

then this is what the formula returns...

SIX SEVEN EIGHT, ONE TWO THREE FOUR FIVE

The last three words were taken from the back, placed in the front with a comma/space between them (and all characters were made upper case)... isn't that what you wanted?

 

[facethegod]

try this..

UPPER(MID(TRIM(A1)&","&TRIM(A1),FIND("$",SUBSTITUTE(TRIM(A1)," ","$",LOOKUP(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1,{0,4,6},(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))-{0,1,2})))+1,LEN(TRIM(A1))))

HOPE this helps..

 

[shrinivasmj]

If i take a name as

Kumar anil t. sing
the out put is
SING,KUMAR ANIL T.

IT SHOULD COME AS
T. SING,KUMAR ANIL

 

[Rick Rothstein]

If i take a name as

Kumar anil t. sing
the out put is
SING,KUMAR ANIL T.

IT SHOULD COME AS
T. SING,KUMAR ANIL

That is how it comes out on my system! Are you using this formula that I posted?

=UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),(1+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>2)+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>4))*99))&", "&A1,LEN(A1)+1))

Make sure to copy/paste it... don't try to re-type it as you might think a space is an empty string depending on how the formula breaks when word wrapping.

EDIT NOTE
------------------------
I notice you do not put a space after the comma. If that is what you actually want, then use this formula...

=UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),(1+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>2)+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>4))*99))&","&A1,LEN(A1)))

Note that there are two changes between the above formulas... one, the space after the comma is removed; and two, the 1 that is added at the end of the formula is removed (if you don't remove it, then removing only the space after the comma leaves a space at the end of the text the formula outputs).