Add a comma after name

S

shadyshawn

New Member
Joined
Dec 28, 2010
Messages
46
Hi I just joined MREXCEL club after I found most of my excel answers from here when googling, and found folks here are really helpful.

As my first thread and question, I would like to learn how to add a comma after the first name in the cell. I have a list of names in one column as LASTNAME FIRSTNAME. I want to add a comma after the last name, without doing it in a new column. I'm assuming this has to be done thru VB? but I dont know anything about the codes yet. If anyone could help me out I would truly appreciate it!! Also, I want to add a comma after the last name only; so if I have a name that has three parts (e.g Jane Doe Jr.), only one comma is added after "Jane".

Thanks in advance for any help!
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Hello, and welcome to the board!
You don't need VB to do what you're looking for, if you have a 2nd column you can use. The one complication will be if someone has a 2-part last name with a space. Assuming the list is in column A, and the output can be in Column B, the following formula will put a comma before the 1st space:
Code: 
=LEFT(A1,FIND(" ",A1,1)-1) & "," & MID(A1,FIND(" ",A1),5)
Copy the formula to all applicable rows.
If you can't put this in a 2nd column (you want to add the commas directly to the data in the existing column) you will need VB.
Cindy
 
Last edited:
Upvote 0
Thanks a lot for replying, Cindy!! I learned how to use the formula you wrote previously. But unfortunately for this one I need to add the comma directly into the data, so I'm thinking it has to be done thru VB, just dont know how...
 
Upvote 0
heya

erm jus been messin round did solution based on simple text functions that can be used on the sheet. possible a better method using function i don't know but this worked for me :)

Code: 
Sub test()

Dim aaa As Long
Dim bbb As Long
aaa = 1

Do Until Range("a" & aaa) = ""
    bbb = Application.WorksheetFunction.Find(" ", Cells(aaa, 1), 1)
    Range("a" & aaa) = (Left(Range("a" & aaa), (bbb - 1))) & ", " & Right(Range("a" & aaa), (Len(Range("a" & aaa)) - bbb))
    aaa = aaa + 1
Loop

End Sub

this starts at cell A1 and works down doing the process utill it reaches a blank cell. to start process from a different row jus change the value of "aaa = " to your first row.

everywhere "a" is seen would need to be changed if using a different column

just hit [ALT + F11], insert module, paste this code
then back in excel tools>macro>macros select the macro (test) and hit run

hope it helps :)
 
Upvote 0
Give this a try...
Code: 
Sub TestThis()
Dim c As Range, CommaSpot As Integer, LastDataRow As Long
LastDataRow = ActiveSheet.Range("A65535").End(xlUp).Row 'Change 65535 to a larger number if you have more rows
For Each c In Range("A1:A" & LastDataRow)
    CommaSpot = InStr(1, c.Value, " ")
    If c.Value <> "" Then
    c.Value = Mid(c.Value, 1, CommaSpot - 1) & "," & Mid(c.Value, CommaSpot, 50)
    End If
Next c
End Sub
You'll need to modify it for the appropriate column.
 
Upvote 0
Thanks a lot, motmit!! It's almost there....

So when I test it by placing a list of name in Column A, it worked great. But in the report I have, the list is in column G, which is the only column I need to add the comma into. So I changed "a" to "g", but then got an error when applying the code. Anything I missed there?
 
Upvote 0
Cindy Ellis said:
Give this a try...
Code: 
Sub TestThis()
Dim c As Range, CommaSpot As Integer, LastDataRow As Long
LastDataRow = ActiveSheet.Range("A65535").End(xlUp).Row 'Change 65535 to a larger number if you have more rows
For Each c In Range("A1:A" & LastDataRow)
    CommaSpot = InStr(1, c.Value, " ")
    If c.Value <> "" Then
    c.Value = Mid(c.Value, 1, CommaSpot - 1) & "," & Mid(c.Value, CommaSpot, 50)
    End If
Next c
End Sub
You'll need to modify it for the appropriate column.
Click to expand...

THAT WORKS LIKE A CHARM!!! THANK YOU SO MUCH, Cindy!!!!
 
Upvote 0
sorry my mistake i had left cells(aaa,1) in which was pointing to column a still

anyway changing the column to g and using the instr function which i hadnt come accross (thanks cindy) (Y)

this is what i get:
Code: 
Sub test2()

Dim aaa As Long
Dim bbb As Long
aaa = 1

On Error Resume Next
Do Until Range("g" & aaa) = ""
    bbb = InStr(Range("g" & aaa), " ")
    Range("g" & aaa) = (Left(Range("g" & aaa), (bbb - 1))) & ", " & Right(Range("g" & aaa), (Len(Range("g" & aaa)) - bbb))
    aaa = aaa + 1
Loop

End Sub
 
Last edited:
Upvote 0
Hi shadyshawn

Here's another approach
Code: 
Option Explicit
Sub SplitText()
    Dim c As Range
    Dim Sp As Variant
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Sp = Split(c.Value, " ")
        c.Value = Sp(0) & ", " & Strip(c.Value, Sp(0))
    Next c
End Sub
 
 
Public Function Strip(psLine As String, psRemoveStr As Variant) _
        As String
' From [URL]http://www.freevbcode.com/ShowCode.asp?ID=4662[/URL]
    Dim iLoc As Integer
    iLoc = InStr(psLine, psRemoveStr)
    Do While iLoc > 0
        psLine = Left(psLine, iLoc - 1) & _
                Mid(psLine, iLoc + Len(psRemoveStr))
        iLoc = InStr(psLine, psRemoveStr)
    Loop
    Strip = psLine
End Function

Change this range as appropriate
Code: 
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))

John
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Lookup Data Across Several Sheets
Replies
1
Views
134
NickFru
N
F
  • Question
Assigning a "cleaners" word for every worksheet every 7 days and moving it to another person after a week.
2
Replies
16
Views
384
FlipEternalX
F
K
  • Solved
populating a textbox on a userform from selections that are made from a listbox that is one another userform (popup)...
Replies
4
Views
196
kbishop94
K
M
  • Question
Using Filter and VSTACK on a Search Box with Multiple Conditions?
Replies
4
Views
885
Fluff
Fluff
F
  • Question
Changing 2 worksheet name at the same time
Replies
7
Views
238
Micron
M

Forum statistics

Threads
1,215,011
Messages
6,122,677
Members
449,092
Latest member
tayo4dgacorbanget

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top