Close Menu
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook X (Twitter) Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home » Office

How to Separate Names in Excel

Asmi DhakalBy Asmi DhakalOctober 5, 2023
How to separate names in excel

For longer name lists, use automation tools like Text to Columns and Power Query to separate names in Excel.

Additionally, you can use the LEFT, RIGHT, SEARCH, and MID functions to construct a formula to separate names in Excel.

Note: We’ve assumed that your names are separated by space in between. If you have a different delimiter (e.g., a comma), simply swap the space character with your delimiter.

Table of Contents

  • Use Text to Columns to Separate Names in Excel
  • Use Library Functions to Separate Names in Excel
    • Separate First Name
    • Separate Middle Name Initial
    • Separate Last Name
  • Use Power Query to Separate Names in Excel
  • Assign a Macro to Separate Names in Excel
  • Use Third-Party Add-ins to Separate Names in Excel

Use Text to Columns to Separate Names in Excel

The Text to Columns tool is an extremely handy tool for data parsing. Using a delimiter, you can use this utility to divide names into different columns.

This tool will divide any data between the set symbols into separate columns. I recommend you use this method if there are no middle names in your name list.

  1. Select your range.
  2. Head to the Data tab and select Text to Columns from Data Tools.
    Text to Columns Excel
  3. In Step 1 of 3, choose Delimited > Next.
    Text to Columns Step 1 of 3
  4. In Step 2 of 3, select Space > Next.
    Text to Columns Step 2 of 3
  5. In Step 3 of 3, click on the box next to General.
  6. Specify the location you wish to place your columns next to Destination.
    Text to Columns Step 3 of 3
  7. Click Finish.

Use Library Functions to Separate Names in Excel

You can also nest the LEFT, RIGHT, SEARCH, and MID functions to create a formula to separate names in Excel.

If you’re not familiar with these functions, here are their arguments and, a little bit about what they do:

FunctionFormatDescription
LEFT=LEFT(text, [num_chars])Extracts a specified number of characters from the left.
RIGHT=RIGHT(text, [num_chars])Extracts a specified number of characters from the right.
MID=MID(text, start_num, num_chars)Extracts a specified number of characters from the set position.
SEARCH=SEARCH(find_text, within_text, [start_num])Returns the number of characters on the left of the specified character.

Here, we have a spreadsheet with full names in column A. We will use three formulas to extract the first, middle, and last names in columns B, C, and D.

Excel Spreadsheet

Separate First Name

  1. On your workbook, select an empty cell.
  2. Enter the formula in this format to extract the first name:
=LEFT(A2, SEARCH(" ", A2) - 1)
Separate First Name
  1. Use Flash Fill to apply the formula to the entire column.

Separate Middle Name Initial

  1. Open your workbook and select a new cell.
  2. As not all names have a middle name, we’ll be nesting our formula inside IFERROR:
=IFERROR(MID(A2,SEARCH(".",A2)-1,2), " ")
Separate Middle Name
  1. Use Flash Fill to fill in the remaining columns.
Note: If you choose not to use the IFERROR function, names with no middle initials will return the #VALUE! error.

Separate Last Name

  1. On the Excel grid, select a new cell.
  2. Enter the formula in the following format:
=IF(C2=" ",RIGHT(A2, LEN(A2) - SEARCH(" ", A2)),RIGHT(A2, LEN(A2) - SEARCH(" ", A2)-3))
Separate Last Name Excel
  1. Use the fill handle to extract last names from the full name.

We used the IF function because there are two instances. Earlier, we used the IFERROR function to return space if there’s no middle initial. If the area has space, we will be using the first formula to extract the last name.

Otherwise, we will be removing 3 characters from the left when extracting the last name. This will subtract the middle initial and space when returning the last name.

Use Power Query to Separate Names in Excel

Power Query is a powerful automation utility in MS Excel. If you’re using the 2016 version of Excel or later, you can use Power Query to separate names in Excel.

  1. On your workbook, select a cell from the table.
  2. Head to the Data tab.
  3. Select From Table in the Get & Transform section.
    Get data from table or range
  4. If prompted, click OK on the Create Table pop-up.
    Create Table popup
  5. Select a cell from the column with full names 
  6. From the Home tab, select Split Columns > By Delimiters.
    Split columns by delimiter power query
  7. Specify your separator in the window.
  8. If your data has,
  • Only First and Last names: Choose Each occurrence of the delimiter
    Split at each delimiter power query
  • First, Middle, and Last Name:
    a. Select Right-most delimiter.
    Separate at Right most delimiter
    b. Repeat steps 6 and 7 then choose Left-most delimiter.
    Split left most delimiter power query
  1. Click OK.
  2. From the top-left corner of the Power Query, select Close and Load.

Assign a Macro to Separate Names in Excel

If you regularly have to separate first and last names, you can create a macro to automate this task. 

In Excel, Macros register your keystrokes to create a Visual Basic code. Excel will create a code using these movements and replicate them when you run your macro.

  1. If you haven’t already, enable the Developer tab.
  2. From the Developer tab, select Record Macro.
    Record Macro Excel
  3. Give your macro a name, and assign a Shortcut key.
    Record Macro SHortcut
  4. Use Text to Columns to separate names.
  5. From the Code section, click on Stop Recording.
    Stop recording macro
  6. To run the macro, simply use the assigned key combination as a shortcut.

Use Third-Party Add-ins to Separate Names in Excel

You can also use third-party add-ins to separate names in Excel. Using third-party add-ins is more convenient than using the Text to Column tool or the Excel functions.

However, you must be aware that third-party add-ins can put your device at risk of malware.

Excel
Asmi Dhakal
  • X (Twitter)
  • LinkedIn

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals. You can contact her at asmi@technewstoday.com

Related Posts

how to stop spam emails

How To Stop Spam Emails? 6 Quick Ways

November 24, 2023
how to unprotect sheet

How to Unprotect Excel Sheet in 4 Ways

October 16, 2023
password protect google doc

How To Password Protect Google Doc

October 5, 2023
How to set print area in excel

How to Set Print Area in Excel?

October 6, 2023
How to remove formatting in Excel

How to Remove Formatting in Excel?

October 2, 2023
How to insert a checkbox in word

How to Insert a Checkbox in MS Word

October 3, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
scanning and repairing drive stuck

Windows Stuck in Scanning and Repairing Drive Screen? 4 Easy Fixes

December 4, 2023
computer died

Computer Died? Here’s What You Should Do Next

December 1, 2023
how-to-share-a-printer

How to Share a Printer on Network (Step-by-Step Guide)

November 28, 2023
You may also like
printer-not-printing

How to Fix a Printer That’s Not Printing

September 26, 2023
Share Location With Friends and Family

Share Location With Friends and Family (iPhone and Android)

September 18, 2023
how-to-print-without-a-printer

How to Print Without a Printer

September 18, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook X (Twitter) Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday, editor@technewstoday.com | Tech Central Pvt. Ltd.

Type above and press Enter to search. Press Esc to cancel.