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

kimsa

New Member
Joined
Feb 26, 2016
Messages
15
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,101
Office Version
2013
Platform
Windows
If you can use vba, copy to standard code module.

Code:
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

Well-known Member
Joined
May 31, 2015
Messages
1,604
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

Well-known Member
Joined
May 31, 2015
Messages
1,604
Oh, I forgot. If you want to replace the original, you'll need to copy and paste as values
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Another formula solution:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Smith, Mary</td><td style=";">Smith, M</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Smith, Mary / Jones, Sam</td><td style=";">Smith, M ; Jones, S</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet566</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">LEFT(<font color="Red">A1,FIND(<font color="Green">",",A1</font>)+2</font>)&IFERROR(<font color="Red">LEFT(<font color="Green">MID(<font color="Purple">A1,FIND(<font color="Teal">"/",A1</font>),99</font>),FIND(<font color="Purple">",",A1,FIND(<font color="Teal">"/",A1</font>)</font>)-FIND(<font color="Purple">"/",A1</font>)+3</font>),""</font>),"/"," ;"</font>)</td></tr></tbody></table></td></tr></table><br />
 

kimsa

New Member
Joined
Feb 26, 2016
Messages
15
Hi,

Another formula solution:

AB
1Smith, MarySmith, M
2Smith, Mary / Jones, SamSmith, M ; Jones, S

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet566

Worksheet Formulas
CellFormula
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

New Member
Joined
Feb 26, 2016
Messages
15
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
 

Forum statistics

Threads
1,078,437
Messages
5,340,277
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top