find and replace - text strings

Not an easy way, but if you can sort your list so that you can split the names with 2 caps and 1 cap following the names you could use this one for 2 caps following

=LEFT(A1,LEN(A1)-2)&", "&LEFT(RIGHT(A1,2),1)&". "&RIGHT(RIGHT(A1,2),1)&"."

and with 1 cap following

=LEFT(A2,LEN(A2)-1)&", "&LEFT(RIGHT(A2,1),1)&". "

Sorry OldChippy, for butting in, but since I've spent a fair amount of time on this, maybe yabai1974 could try this macro ...

Sub PunctuateNames()

MinCell = 1
MaxCell = 100

For CellRow = MinCell To MaxCell

For CharNo = 1 To Len(Cells(CellRow, 1))

If CharNo = Len(Cells(CellRow, 1)) Then
CellContents = CellContents & ". " & Mid(Cells(CellRow, 1), CharNo, 1) & "."
Else
If CharNo = 1 Then
CellContents = CellContents & Mid(Cells(CellRow, 1), (CharNo), 1)
Else
If (Asc(Mid(Cells(CellRow, 1), CharNo)) < 96) And _
Asc(Mid(Cells(CellRow, 1), CharNo - 1)) > 96 Then
CellContents = CellContents & ", " & Mid(Cells(CellRow, 1), CharNo, 1)
Else
CellContents = CellContents & Mid(Cells(CellRow, 1), (CharNo), 1)
End If
End If
End If

Next CharNo

Cells(CellRow, 1) = CellContents
CellContents = ""
Next CellRow

End Sub


Yabai1974 - you will need to set MinCell to the first row number for your cells and MaxCell to whatever your last row number is for the cells you want to convert.

Hi Peter, you gave me an idea there with your macro, so adjusting my formula for either senario

Please Login or Register  to view this content.

Glad to help, between us all we can find a number of solutions - thanks for the feedback and thanks PeterB for your macro idea