MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Concatenate


Posted by Ernie on January 30, 2002 4:33 AM

I have a datasheet with about 450 names. In sheet 1 column A I have the names like -Jose P. Licaros, I want to make on sheet 2 column A with this format: Licaros, Jose P. How possible to do this in excel?

Thanks,

Ernie


Posted by JohnG on January 30, 2002 6:52 AM

Try this
Sub NameSwap()
Dim FinalRow As Integer
Dim NSLoop As Integer
Dim Zloop As Integer
Dim TmpEndString As String
Dim TmpStrtString As String
Dim NSstring As String

FinalRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
For NSLoop = 1 To FinalRow
NSstring = Worksheets("Sheet1").Range("A" & NSLoop).Value
TmpEndString = ""
TmpStrtString = ""
For Zloop = Len(NSstring) To 1 Step -1
If Mid(NSstring, Zloop, 1) <> Chr$(32) Then
TmpEndString = Mid(NSstring, Zloop, 1) & TmpEndString
Else
TmpStrtString = Mid(NSstring, 1, Zloop)
NSstring = TmpStrtString & TmpEndString
Worksheets("Sheet2").Range("A" & NSLoop).Value = NSstring
Exit For
End If
Next Zloop
Next NSLoop

End Sub

Posted by Mark W. on January 30, 2002 9:13 AM

You could use the array formula...

{=TRIM(RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*ROW(INDIRECT("1:"&LEN(A1)))))&", "&MID(A1,1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*ROW(INDIRECT("1:"&LEN(A1))))))}

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.