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

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.



--
Message posted from http://www.ExcelForum.com

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).Ran ge("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

"Todd Huttenstine" wrote in message
...
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

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 " wrote in message
...
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
Next
--------------------

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

K


---
Message posted from http://www.ExcelForum.com/

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.



--
Message posted from http://www.ExcelForum.com