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 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»Software»Excel»How to Separate Names in Microsoft Excel

How to Separate Names in Microsoft Excel

Asmi DhakalBy Asmi DhakalDecember 4, 2022
how-to-separate-names-in-excel

When we copy-paste names to the Excel grid, the full name is often pasted on a single column. It can be a hassle sorting data into separate columns, especially for bigger files. As this problem is quite common, Excel has built-in features to separate texts.

Excel offers built-in features to separate first, middle and last names. Keep reading this article to separate names in Excel depending on the separator you’ve used to separate the words.

Table of Contents

  • How to Separate Names in Excel?
    • Use Text to Columns
    • Use Library Functions
    • Use Power Query
    • Use Third Party Add-ins
  • How to Automate Separating Names by Creating a Macro?

How to Separate Names in Excel?

If you wish to separate names into separate columns, you can use the Text to Column tool to separate each text. You can also use formulas using functions like LEFT/RIGHT, LEN, SEARCH, and SUBSTITUTE to separate first middle and last names. Then, you can use Flash Fill to fill in the rest of the cells. 

Similarly, you can use the Power Query to separate full names if you’re an advanced user.

Use Text to Columns

The Text to Columns tool in Excel changes the alignment of your cell value and can also be used to add space between your names. When using this tool, your information will be separated into two columns.

  1. Launch Excel.
  2. Select a cell from the table
  3. Head to the Data tab, then locate and select Text to Columns.
    Text to Columns Tool Excel
  4. In Step 1 of 3 of the Convert Text to Columns Wizard, select the box next to Delimited.
    texttocolumnwizard 1of3
  5. Select Next.
  6. In Step 2 of 3, check the box next to Space > Next. If some other separator separates your text, select the box next to the name of the separator > Next.
    texttocolumnwizard 2of 3
  7. In step 3 or 3, select the box next to General.
    TextToWizard3of3
  8. Next to the Destination section, enter the column you wish to enter the separated data.
  9. Choose Finish.

Use Library Functions

You can use functions readily available in Excel to extract first and last names, separated by a space or comma. Additionally, you can also extract middle names that are separated by a space.

In the given formulas, in place of the “cell”, enter the location of the cell with the full name.

Separated by Space

If a space separates the full names, you can use these steps to extract the first, last and middle names into different columns.

First Name

  1. On your workbook, select an empty cell.
  2. Enter the formula in this format to extract the first name:
    =LEFT(cell, SEARCH(" ", cell) - 1)
    Seperate first name by space
  1. Use Flash Fill to fill in the rest of the column.

Middle Name

  1. Open your workbook and select a new cell.
  2. Enter your formula in the following format:
    =RIGHT(cell, LEN(cell) - SEARCH("#", SUBSTITUTE(cell," ", "#", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))
    Seperate middle name by space
  1. Use Flash Fill to fill in the remaining columns.

Last Name

  1. On the Excel grid, select a new cell.
  2. Enter the formula in the following format:
    =RIGHT(cell, LEN(cell) - SEARCH(" ", cell))
    Seperate last name by space
  1. Use Flash Fill to extract last names from the full name.

Separated by Comma

You will have to use a different formula to extract the first and last names if commas separate your full names.

First Name

  1. On your Excel grid, select a new cell.
  2. Use this format to enter the formula to separate names by commas:
    =LEFT(cell, SEARCH(",", cell) - 1)
    Seperate first name by comma
  1. Use Flash Fill in the rest of the columns.

Last Name

  1. Select an empty cell from your workbook.
  2. Enter the formula in this format to extract the last names:
    =RIGHT(cell, LEN(cell) - SEARCH(",", cell))
    Seperate last name by comma
  1. Use Flash Fill to fill the remaining columns.

Use Power Query

If you’re using the 2016 version of Excel or later, you can use Power Query to split the full names into different columns. As using Power Query is a bit complicated, we advise only advanced Excel users to use this method.

Before opening your data in Power Query, convert your data to a table. Select your data from the grid, then head to the Insert tab, then select Table. After you’ve converted your data to a table, you can load your table into the Power Query.

  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.
    from-table-and-range
  4. Select a cell from the column with full names 
  5. From the Home tab, select Split Columns > By Delimiters.
    Split Columns Power Query
  6. Specify your separator in the window, then Click OK.
    Split Columns Power Query2
  7. From the top-left corner of the Power Query, select Close and Load.

Use Third Party Add-ins

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.

How to Automate Separating Names by Creating a Macro?

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. When you record a Macro, Excel will generate a code following your keystrokes that you can run to replicate the action.

After you record your Macro from the developer tab, you can continue to separate the full names from the grid following the above-mentioned methods. After you record your Macro, you can run the Macro the next time you need to separate names.

.

how-to
Asmi Dhakal
  • 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

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
how long does a cmos battery last

How Long Does a CMOS Battery Last

January 25, 2023
thunderbolt vs usb c

Thunderbolt Vs USB C: What’s the Difference

January 25, 2023
how to find hidden folder in laptop

How to Find Hidden Folder in Laptop

January 24, 2023
You may also like
how-to-connect-headphones-to-tv

5 Ways to Connect Headphones to TV

January 29, 2023
connect-airpods-to-samsung-tv

How to Connect AirPods to Samsung TV? (Step-by-Step Guide)

January 29, 2023
AIO Pump Not Working

AIO Pump Not Working? Here’s How to Fix It

January 29, 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 Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday.

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