Removing Middle Initial from Last Name, First Name

kronious

New Member
Joined
May 6, 2019
Messages
4
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...
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
with PowerQuery (Get&Transform)

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

Column1Column1
Smith, John QSmith, John
 
Last edited:
Upvote 0
Or with plain VBA:
Code:
Sub Demo()
With ActiveCell
  .Value = Split(.Text, ", ")(0) & ", " & Split(Split(.Text, ", ")(1), " ")(0)
End With
End Sub
 
Upvote 0
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...
 
Upvote 0
Thank you, Rick! I had a feeling it would be a LEN formula, but I wasn't sure how simple it would be...
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top