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.
