Add a comma after name

[shadyshawn]

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!

 

[shadyshawn]

Running into another problem... if one of the names in the list already has a comma after the first name, it would add another comma into the cell. Is there anyway to make the VB not to add comma if there is already one existing?

Thanks guys!

 

[Cindy Ellis]

Minor modification to the code...only add the comma if there's not already one there:

Sub TestThis()
'declare the variables
Dim c As Range, CommaSpot As Integer, LastDataRow As Long 
 
LastDataRow = ActiveSheet.Range("G65535").End(xlUp).Row 
 
'If the cell isn't empty, and doesn't already contain a comma, 
'then add a comma just before the first space
 
For Each c In Range("G1:G" & LastDataRow)      
    If c.Value <> "" And InStr(1, c.Value, ",") = 0 Then  
        CommaSpot = InStr(1, c.Value, " ")                   
        c.Value = Mid(c.Value, 1, CommaSpot - 1) & "," & Mid(c.Value, CommaSpot, 50)           
    End If
Next c
End Sub

 

[shadyshawn]

Minor modification to the code...only add the comma if there's not already one there:

Sub TestThis()
'declare the variables
Dim c As Range, CommaSpot As Integer, LastDataRow As Long 
 
LastDataRow = ActiveSheet.Range("G65535").End(xlUp).Row 
 
'If the cell isn't empty, and doesn't already contain a comma, 
'then add a comma just before the first space
 
For Each c In Range("G1:G" & LastDataRow)      
    If c.Value <> "" And InStr(1, c.Value, ",") = 0 Then  
        CommaSpot = InStr(1, c.Value, " ")                   
        c.Value = Mid(c.Value, 1, CommaSpot - 1) & "," & Mid(c.Value, CommaSpot, 50)           
    End If
Next c
End Sub

You are amazing Cindy! That works great on the report. Now another question if you dont mind...I tried to test it on another list of names in a different report, and it has about 18000 names in that list. When I run the VB, it only applied to 14400 or so of the names, and returened an error msg "Run time error '5'. Invalid procedure call or argument". Do I need to modify the code again?

 

[Cindy Ellis]

Thanks for the nice words . Now let's see if we can solve this bit of the problem...
I can recreate that error message if I run it with a name that doesn't have a space character in it. Can you verify that the name after the last successful one doesn't have a space?
The following code adds a test for no space character, and will prompt you with the row number so you can take a look at it when the macro completes.

Sub TestThis()
Dim c As Range, CommaSpot As Integer, LastDataRow As Long
LastDataRow = ActiveSheet.Range("G65535").End(xlUp).Row 'Change 65535 to a larger number if you have more rows
For Each c In Range("G1:G" & LastDataRow)
    If c.Value <> "" And InStr(1, c.Value, ",") = 0 Then
        CommaSpot = InStr(1, c.Value, " ")
        If CommaSpot <> 0 Then
            c.Value = Mid(c.Value, 1, CommaSpot - 1) & "," & Mid(c.Value, CommaSpot, 50)
        Else
            MsgBox "Row " & c.Row & " doesn't have a space."
        End If
    End If
Next c
End Sub

If you don't want the prompt, just comment out the "MsgBox" statement.
Hope this helps,
Cindy

 

[shadyshawn]

Ah..that's exactly what happened, there were a few names that only have one name in it so there was no space after that. Thanks again, Cindy

 

[Cindy Ellis]

Glad to help.
The only thing that might still be an issue (it won't cause an error, but would be incorrect) is if the last name has 2 or 3 parts separated with a space... such as "van der Walker".
So, you'll still need to review the list for sanity. I'm sure I could count the spaces and flag those with more than 1, but I'm on my way out of town and need to log off
Cindy

 

[Scott Huish]

This will add the comma at the position of the last space in the cell. This should eliminate the problem of the multi word last name, only giving the problem if you should have a multi word first name, which is probably less common:

Function AddComma(r As String) As String
Dim t
r = Trim(Replace(r, ",", ""))
t = Split(r)
If UBound(t) > 0 Then t(UBound(t) - 1) = t(UBound(t) - 1) & ","
AddComma = Join(t)
End Function

Sub AddCommaToName()
Dim c As Range
For Each c In Range("G1:G" & Range("G" & Rows.Count).End(xlUp).Row)
    c = AddComma(c.Text)
Next
End Sub

 

[motmit]

This will add the comma at the position of the last space in the cell. This should eliminate the problem of the multi word last name, only giving the problem if you should have a multi word first name, which is probably less common:

cus i like messin with new code/methods (always learning) but anyway testing this code sos but it seems it does the opposite of what you say :

it deals with muti word first names but not multi last names:

Excel Workbook
	F	G
1	name1	name1
2	name1 name2	name1, name2
3	name1 name2 name3	name1 name2, name3
4	name1 name2 name3 name4	name1 name2 name3, name4
Sheet1

Excel 2003

 

[Scott Huish]

According to your description it does exactly what I said because you have LASTNAME listed first:

LASTNAME FIRSTNAME

 

[motmit]

ah yes mixed up what i saying, so ur code does what you said it should, but it does not do what was origionally requested

example in first post was 'jane doe jr' with the comma appearing after jane, so your code does not achieve the desired outcome.