Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home

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?

Re: last_name,first_name:reformatting to first_name last_name

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

Dave


Now

OzGrid Business Applications


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

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

OzGrid Business Applications


Re: last_name,first_name:reformatting to first_name last_name

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 <> ""
ActiveCell.Select

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
Loop

End Sub


Re: last_name,first_name:reformatting to first_name last_name

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)
Columns("A:B").Delete
End With
Application.DisplayAlerts = True
End Sub

Just make sure columns B and C are clear.


Dave

OzGrid Business Applications


Re: Oops, make that: =B1 &

Posted by david on February 15, 2001 6:03 AM
so simple - yet so effective
WORKED GREAT - THANKS!!!



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.