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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
563
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
299
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,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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