Split names into two columns: 1st column has 1st and middle name; 2nd column has the surname.

M

maxim642

Board Regular
Joined
Feb 4, 2021
Messages
91
Office Version
  1. 365
Platform
  1. MacOS
Full nameFirst & MiddleSurname
John Jack MurphyJohn JackMurphy
Mary-Joe SpencerMary-JoeSpencer
Jordan PetersJordanPeters
Jamie John-PaulJamieJohn-Paul

The tricky part is where some names have a hyphen (so they would be the first name), or other entries that don't have a middle name.

The above table provides examples of how it should look. I just don't know the formula to achieve it in the last two columns.
 
Sort by date Sort by votes

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
You must log in or register to reply here.

Similar threads

M
  • Question Question
How to split a full name in 1 column into 2 columns (C1 - First name, C2 - Last name (*but here's the tricky - what if there's a middle initial?
Replies
6
Views
383
Mavericx
M
J
  • Solved
Match Data and Input Value
2
Replies
11
Views
2K
Fluff
Fluff
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
288
Peter_SSs
Peter_SSs
T
  • Question Question
Extracting First Name and Surname from Email Addresses
Replies
7
Views
4K
Fluff
Fluff
S
  • Solved
Need formula to compare text in two cells, indicate if they match, not move when corrections are inserted
Replies
2
Views
4K
Synthia
S

Forum statistics

Threads
1,223,884
Messages
6,175,171
Members
452,615
Latest member
bogeys2birdies

Share this page

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