Code modification - remove commas from names

[Todd Huttenstine]

The below code looks in a specified dynamic range and for
each item(Persons name) in the range, it removes the comma
and then flip flops the name to FIRST LAST from LAST
FIRST. It works perfectly for people who do not have a
middle initial but for people who have a middle initial,
it keeps it there. For example the code as it is now will
convert the name Alexander, Tim into Tim Alexander.
However a person who has a middle initial is different.
If Tims name were Alexander, Tim C. the code would convert
it to Tim C. Alexander. I want his name to to be like the
rest which would be Tim Alexander.


How would I modify the below code to accomplish this?

numcount = Application.WorksheetFunction.CountA(Sheets
("Converted Data").Range("F:F"))
For Each cell In Sheets("Converted Data").Range("F2:F" &
numcount)
If Not IsEmpty(cell) Then
If InStr(cell, ",") Then
sStr = Right(cell, Len(cell) _
- InStr(cell, ","))
cell.Value = Trim(sStr) & " " & _
Trim(Left(cell, _
InStr(cell, ",") - 1))
End If
Else
Exit For
End If
Next



Thank you

Todd Huttenstine

 

[kkknie]

This does it for me.

Code
-------------------
For Each cell In Range("A1:A10")
If Not IsEmpty(cell) Then
If InStr(cell, ",") Then
sStr = Right(cell, Len(cell) - InStr(cell, ","))
If InStr(cell, " ") Then
sStr = Trim(sStr)
sStr = Left(sStr, InStr(sStr, " ") - 1)
End If
cell.Value = Trim(sStr) & " " & Trim(Left(cell, InStr(cell, ",") - 1))
End If
Else
Exit For
End If
Nex
-------------------

One item to mention is that I wouldn't use the variable cell in you
code since it is so close to an actual defined object (in fact, I'
suprised excel let you get away with it). Just a tip to avoi
confusion later if you start using the Cells range object.

 

[Greg Koppel]

Hi Todd,

You want to use the InStr function again to find the blank in sStr. I
changed the column from your code.

Sub test()
numcount = Application.WorksheetFunction.CountA(Sheets(1).Range("A:A"))
For Each cell In Sheets(1).Range("A1:A" & numcount)
If Not IsEmpty(cell) Then
If InStr(cell, ",") Then
sStr = Right(cell, Len(cell) - InStr(cell, ","))
'MsgBox sStr
If InStr(sStr, " ") Then
sStr = Trim(sStr)
newStr = Left(sStr, Len(sStr) - InStr(sStr, " ") + 2)
End If
cell.Value = Trim(newStr) & " " & Trim(Left(cell, InStr(cell,
",") - 1))
End If
Else
Exit For
End If
Next
End Sub

HTH, Greg

 

[Bob Phillips]

cell is not a defined object, not a reserved word, and is not a problem.
Personally I use it all the time to refer to a cell within a range in
exactly this way, and it is has good annotation qualities. Excel also
upshifts cells to Cells, and in the VBE you can colour code keywords, so
cell is fine.

Use with impunity.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[kkknie]

Just stating an opinion about the cell variable name, not stating tha
it was an object or reserved word (else it wouldn't work at all).
find that the code:

For each cell in Range(R1)
Cells(cell.Row,3).Interior.ColorIndex = 33
Next

Is not as readable *to me* versus using c as the range variable.
also avoid the use of variable names like Sheeet, Applicatin, Ragne
Workbouk and other things that look like objects.

Once again, just my opinion.