MrExcel Publishing
Your One Stop for Excel Tips & Solutions

last_name,first_name:reformatting to first_name last_name

Posted by David on February 14, 2001 8:38 AM

I have downloaded data into an Excel spreadsheet, but the names are in the format of Last,First. Is there a way to format the column to get First Last?

Posted by Dave Hawley on February 14, 2001 8:48 AM

Hi Dave

Assuming your Column is A.

select the Column then go to Data>Text to columns. Click "Delimited", Next, Check Space, Finish.

Now in cell C1 put:
=B1 & " " & C1
copy down. Now copy column C and pastespecial as values.

Any good



OzGrid Business Applications

Posted by Dave Hawley on February 14, 2001 8:50 AM

Oops, make that: =B1 & " " & A1

OzGrid Business Applications

Posted by Faster on February 14, 2001 5:18 PM

Sub NameFormat()
'From my stuff
'Formats LastName, FirstName to FistName LastName
'Adjust code for spacing as needed

Do While ActiveCell <> ""

Dim MyName As String
MyName = ActiveCell.Text

Dim FirstName As String
Dim LastName As String
Dim MyPos

Dim MySearch As String
MySearch = ","

MyPos = InStr(1, MyName, MySearch, vbTextCompare)
FirstName = Mid(MyName, MyPos + 1, Len(MyName))
LastName = Mid(MyName, 1, MyPos - 1)

Dim MyNewName As String
MyNewName = Trim(FirstName + " " + LastName)

Selection.Offset(1, 0).Select

End Sub

Posted by Dave Hawley on February 14, 2001 5:36 PM

Sub NameFormat()

You could use a macro if you will need to do this often, but I don't know about using a Loop. I would still use Text to columns via VBA, like:

Sub ReverseAndSplit()
Dim LastRow As Long
Application.DisplayAlerts = False
With Columns(1)
LastRow = .Cells(65536, 1).End(xlUp).Row
.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1))
End With
With Range("C1")
.FormulaR1C1 = "=RC[-1] & "" "" & RC[-2]"
.AutoFill Destination:=Range("C1:C" & LastRow)
Range("C1:C" & LastRow).Copy
Range("C1:C" & LastRow).PasteSpecial (xlPasteValues)
End With
Application.DisplayAlerts = True
End Sub

Just make sure columns B and C are clear.


OzGrid Business Applications

Posted by david on February 15, 2001 6:03 AM

Re: Oops, make that: =B1 &

so simple - yet so effective