How to change a full name in cell to only last name first intial

[kimsa]

I have a full name in a cell and want to convert it to last name first initial only in another column

Have:
Smith, Mary

Want:
Smith, M

some cells have:
Smith, Mary / Jones, Sam

Want:

Smith, M; Jones, S

 

[JLGWhiz]

If you can use vba, copy to standard code module.

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

 

[Jeffrey Mahoney]

Does this work?

=IF(IFERROR(SEARCH("/",A3),0)>0,LEFT(A3,SEARCH(",",A3)+2)&"; "&MID(A3,SEARCH("/",A3)+2,SEARCH(",",A3,SEARCH("/",A3))+2-(SEARCH("/",A3)+1)),LEFT(A3,SEARCH(",",A3)+2))

 

[Jeffrey Mahoney]

Oh, I forgot. If you want to replace the original, you'll need to copy and paste as values

 

[jtakw]

Hi,

Another formula solution:

Book1
	A	B
1	Smith, Mary	Smith, M
2	Smith, Mary / Jones, Sam	Smith, M ; Jones, S
Sheet566

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(LEFT(A1,FIND(",",A1)+2)&IFERROR(LEFT(MID(A1,FIND("/",A1),99),FIND(",",A1,FIND("/",A1))-FIND("/",A1)+3),""),"/"," ;")

 

[kimsa]

Hi,

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>

yes this worked great, if I have three names how would I adapt this formula

 

[kimsa]

Yes this worked. I need to know how to use this if three authors are involved

Smith, Mary / Smith, Susy / Smith, Ann

I also forgot to ask how would I invert the author names like below:

Have: Smith, Mary Need: Mary Smith