Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel: First name first and Comma deletion

Posted on 2014-09-17
10
Medium Priority
?
158 Views
Last Modified: 2014-09-17
Hall, Bill
jones, Marcia
Smith, Scott

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

D
0
Comment
Question by:dlewis61
  • 5
  • 3
  • 2
10 Comments
 
LVL 6

Expert Comment

by:Russell Lucas
ID: 40328321
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
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40328346
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40328365
Russell, for some reason your formula leaves the comma at the end of "Marsha Jones,".
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:Martin Liss
ID: 40328371
It also changes "Diddlehopper, John" to  "John Didd".
0
 

Author Comment

by:dlewis61
ID: 40328387
I find that it leaves a zero...
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40328397
I find that it leaves a zero...
What does?

If there's a problem with my code then please attach your workbook.
0
 

Author Closing Comment

by:dlewis61
ID: 40328409
OMG Can't believe it!! The code worked!! Thanks
D
0
 
LVL 6

Expert Comment

by:Russell Lucas
ID: 40328411
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40328502
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
 

Author Comment

by:dlewis61
ID: 40328874
Thank you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question