Removing Middle Initial from Last Name, First Name

[kronious]

Hello,

A similar question was asked a few years ago, but in this case, I have a data sheet of names that look like this:

Smith, John Q

and I just want to remove the middle initial at the end, like:

Smith, John

Seems pretty simple, which is why I haven't quite figured it out yet...

 

[sandy666]

with PowerQuery (Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Extract = Table.TransformColumns(Source, {{"Column1", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}})
in
    Extract[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Smith, John Q[/td][td][/td][td=bgcolor:#E2EFDA]Smith, John[/td][/tr]
[/table]

 

[Rick Rothstein]

Hello,

A similar question was asked a few years ago, but in this case, I have a data sheet of names that look like this:

Smith, John Q

Is the "initial" always a single character? If so...

=LEFT(A1, LEN(A1)-2)

 

[Macropod]

Or with plain VBA:

Sub Demo()
With ActiveCell
  .Value = Split(.Text, ", ")(0) & ", " & Split(Split(.Text, ", ")(1), " ")(0)
End With
End Sub

 

[kronious]

Thank you very much! I work in different versions of Excel, at work this should be easy to use, here at home, I'll add PowerQuery and test it out...

 

[kronious]

Thank you, Rick! I had a feeling it would be a LEN formula, but I wasn't sure how simple it would be...

 

[kronious]

Thank you very much, Paul! Just starting to look at VBA, this is a great help!

 

[sandy666]

I'll add PowerQuery and test it out...

Sure, have a nice day