Solved

Excel: First name first and Comma deletion

Posted on 2014-09-17
10
150 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Accepted Solution

by:
Martin Liss earned 500 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 48

Expert Comment

by:Martin Liss
ID: 40328365
Russell, for some reason your formula leaves the comma at the end of "Marsha Jones,".
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 48

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 48

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 48

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

632 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