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
Smith, Mary	Smith, M
Smith, Mary / Jones, Sam	Smith, M ; Jones, S

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

</tbody>

Sheet566​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUBSTITUTE(LEFT(A1,FIND(",",A1)+2)&IFERROR(LEFT(MID(A1,FIND("/",A1),99),FIND(",",A1,FIND("/",A1))-FIND("/",A1)+3),""),"/"," ;")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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