Excel: First name first and Comma deletion

Hall, Bill
jones, Marcia
Smith, Scott

in excel, how do I make first name first and get rid of the commas?

D
dlewis61Asked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Here is a macro you can use. Change the DATA_COL constant to the coumn where the data is.

Sub UpdateNames()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strParts() As String
Const DATA_COL = "A"

lngLastRow = Range(DATA_COL & "65536").End(xlUp).Row

For lngRow = 1 To lngLastRow
    strParts = Split(Cells(lngRow, DATA_COL).Value, ",")
    If UBound(strParts) > 0 Then
        Cells(lngRow, DATA_COL).Value = strParts(1) & " " & strParts(0)
    End If
Next

End Sub

Open in new window

0
 
Russell LucasIT Infrastructure Project ManagerCommented:
In 2013 you can use flash fill simply by putting the name to correct way next to the first row and clicking the flash fill button.

In formula it's:-

=CONCATENATE(RIGHT(A1,LEN(A1) - FIND(",",A1) - 1)," ",LEFT(A1,LEN(A1) - FIND(",",A1) - 1))
0
 
Martin LissOlder than dirtCommented:
Russell, for some reason your formula leaves the comma at the end of "Marsha Jones,".
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Martin LissOlder than dirtCommented:
It also changes "Diddlehopper, John" to  "John Didd".
0
 
dlewis61Author Commented:
I find that it leaves a zero...
0
 
Martin LissOlder than dirtCommented:
I find that it leaves a zero...
What does?

If there's a problem with my code then please attach your workbook.
0
 
dlewis61Author Commented:
OMG Can't believe it!! The code worked!! Thanks
D
0
 
Russell LucasIT Infrastructure Project ManagerCommented:
You're absolutely right I made a mistake there, here is the correction:-

=CONCATENATE(RIGHT(A1,LEN(A1) - FIND(",",A1) - 1)," ",LEFT(A1,FIND(",",A1)-1))
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 
dlewis61Author Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.