Sub t()
Dim c As Range, spl As Variant
With ActiveSheet
For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
If InStr(c, "/") Then
spl = Split(c.Value, "/")
c.Value = Left(spl(0), InStr(spl(0), ",") - 1) & ", " & Mid(spl(0), InStr(spl(0), ",") + 2, 1) _
& "; " & Left(spl(1), InStr(spl(1), ",") - 1) & ", " & Mid(spl(1), InStr(spl(1), ",") + 2, 1)
Else
c = Left(c.Value, InStr(c.Value, ",") - 1) & ", " & Mid(c.Value, InStr(c.Value, ",") + 2, 1)
End If
Next
End With
End Sub
yes this worked great, if I have three names how would I adapt this formulaHi,
Another formula solution:
A B 1 Smith, Mary Smith, M 2 Smith, Mary / Jones, Sam Smith, M ; Jones, S
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>Sheet566
Worksheet Formulas
Cell Formula B1 =SUBSTITUTE(LEFT(A1,FIND(",",A1)+2)&IFERROR(LEFT(MID(A1,FIND("/",A1),99),FIND(",",A1,FIND("/",A1))-FIND("/",A1)+3),""),"/"," ;")
<thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>