how to switch names from Last, First to First Last
Posted by Kevin on September 18, 2001 6:48 AM
is there a quick way that I can take a block of names and switch there format from 'Last Name, First Name' to 'First Name Last Name'?
Posted by Bill on September 18, 2001 6:52 AM
You could use the Data > Text to Columns wizard to break your column of data into 2 columns, a column with last name and then a column with first name.
Once you get it into two columns, you can use a formula like =A2&" "&B2 to concatenate first and last name.
The final step would be to copy the formula and change it to values using Edit > Copy, Edit > Paste Special > Values > OK.
Posted by Zif on September 18, 2001 8:26 AM
Here are a couple of pieces of VBA code that do the trick. I got these off the web some time ago and have left in the "acknowledgement". This is not my code, just wish I could write code like this!!
NB: These pieces of code switch between the following formats:
Hope this helps
Private Sub Lastname()
'David McRitchie 1999-04-09
'Put cells in range from "FirstName LastName" to "LastName, FirstName"
On Error Resume Next
iRows = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
mrow = lastcell.Row
If iRows > mrow Then iRows = mrow
imax = -1
For ir = 1 To iRows
checkx = Trim(Selection.Item(ir, 1))
L = Len(Trim(Selection.Item(ir, 1)))
If L < 3 Then GoTo nextrow
For im = 2 To L
If Mid(checkx, im, 1) = "," Then GoTo nextrow
If Mid(checkx, im, 1) = " " Then imax = im
If imax > 0 Then
Selection.Item(ir, 1) = Trim(Mid(checkx, _
imax, L - imax + 1)) & ", " & _
Private Sub FirstName()
'David McRitchie 2000-03-23 programming
'Put cells in range from "LastName, FirstName" to "FirstName LastName"
Application.Calculation = xlManual
Dim cell As Range
Dim cPos As Long
For Each cell In Selection.SpecialCells(xlConstants, xlTextValues)
cPos = InStr(1, cell, ",")
If cPos > 1 Then
origcell = cell.Value
cell.Value = Trim(Mid(cell, cPos + 1)) & " " _
& Trim(Left(cell, cPos - 1))
Application.Calculation = xlAutomatic 'xlCalculationAutomatic
Posted by Juan Pablo on September 18, 2001 9:04 AM
If your "Last Name, First Name" is in A1 for example put this in B1 and C1 for "First Name" and "Last Name":