How to Split and Combine Data in Excel?

This guide on how to split data in Excel is suitable for Excel 2016, 2019, 2021 and Excel for Microsoft 365 versions.

In this tutorial, learn how to split data in Excel (and combine it) using Excel text functions and other utilities, as part of a robust data cleaning process.

You’ll learn:

Splitting and Combining Data in Excel – An Overview
How to Split Data in Excel?
How to Combine Data in Excel?

Splitting and Combining Data in Excel – An Overview

Excel contains many functions and utilities to help us split up or combine data. Learning a wide range of text functions is crucial to broadening your knowledge of essential Excel skills.

Text functions are useful in many scenarios particularly as part of the data cleansing process. Often, we may receive data from a colleague or client or import data into Excel from an external system. We need to make sure we are tidying up that data so it is consistent and ready for analysis. Text functions play a huge role in this process as they allow us to do things like remove spaces, remove non-printing characters, change the case, replace values etc.

How To Find Duplicates In Excel? The Best Guide

How to Unmerge Cells in Excel? 3 Best Methods

In this article, we are going to focus on two important tasks: splitting and combining data using text functions and other utilities in Excel.

In the table below, we have the salesperson full name in column A. We will use text functions, text to columns and Flash Fill to split the name into two columns: first name and last name.

In columns C and D, we have a ‘Location Code’ and an ‘ID’. We will use text functions to combine these two strings together to create a ‘Product Code’.

How to Split Data in Excel?

In this section, I’ll walk you through the top three methods of splitting data in Excel. Let us see them one by one.

Method 1 – Use Flash Fill

One of the best additions to Excel in recent years has been Flash Fill. This little button takes all the hard work out of splitting and combining data.

Type the first name into a cell.
Press CTRL+Enter to stay in the same cell.
From the Data tab, in the Data Tools group, click Flash Fill.
Or, press the keyboard shortcut CTRL+E.

*Use the Ctrl+E shortcut to invoke Flash Fill*

Alternatively, you can invoke Flash Fill by typing in the first two names.

Type the first name into a cell.
Start typing the second name into a cell.

Excel will ghost down the remaining names. Check these are correct.

Press Enter to accept.

*Using Flash Fill to split data in Excel*

Method 2 – Use Text to Columns

We can also use the Text to Columns utility to split data into multiple columns.

Select the range of data you want to split up.
From the Data tab, in the Data Tools group, click Text to Columns.

The Convert Text to Columns Wizard will open.

In the Original data type section, select Delimited.
Click Next.
In the Delimiters section, select Space.

*Select an appropriate delimiter in the Text to Columns wizard*

The delimiter is the character that separates each word. In this example, we have a space separating the first name and the last name. This determines where Excel will split the data.

Click Next.
In the Destination area, choose the cell where you want the data to placed.

*Specify the destination cell where the split up data should appear*

Click Finish.
Select Column A and press Delete.

*First names and last names are split using the text-to-columns feature.*

Also Read:

How To Use Excel Countifs: The Best Guide

Excel Conditional Formatting -the Best Guide (Bonus Video)

The Best Excel Project Management Template In 2021

Method 3 – Use Text Functions

Excel contains several text functions that we can use to split up data. We will use LEFT, RIGHT, LEN and FIND to extract the first and last names.

Click in cell B2.

We will use the LEFT function to extract the first name from column A. As each name is a different length, we need to find something in the string that is common to all names in the list, e.g., the space that separates the first name and last name. We will use the space character as a marker and extract everything before the space.

Type =LEFT(A2,FIND(” “,A2))

This formula finds the space “ “ in cell A2 and extracts everything to the left.

Double-click the fill handle to copy down.

Enter the LEFT formula (specified above) to extract the left-half of the string — *Enter the LEFT formula (specified above) to extract the First name*

The formula for extracting the last name is more complex.

Click in cell C2.

We will use the RIGHT function to extract the last name from column A. We need to find the length of the entire string and the location of the space and extract everything in between.

Type =RIGHT(A2,LEN(A2)-FIND(” “,A2))

*Enter the RIGHT formula (specified above) to extract the Last Name*

How to Combine Data in Excel?

Now, let us look at the three easiest methods to combine data in Excel.

Method 1 – Use the CONCAT Function

The CONCAT function, formerly known as CONCATENATE allows us to join multiple strings together. We must specify any spaces or special characters in the formula.

In this example, we need to join the ‘Location Code’ and the ‘ID’ together separated with a dash (-) to form a ‘Product Code’. The format should be ‘ANW-1973’.

Click in cell E3.

The CONCAT function has text arguments. Simply select the cells that contain the text to join together. Remember, the dash counts as text and needs to be contained within quote marks.

*Use the CONCAT formula to combine data in Excel*

Double-click the fill handle to copy the formula down.

*Copy the formula down to all the rows.*

Method 2 – Use Ampersands (&)

We can also join text together using the & symbol. Use an & in between each cell reference and special character.

Click in cell E3.
Type =C2&”-“&D2

*Use the ampersand operator to combine data in Excel*

Method 3 – Use Flash Fill

We can use Flash Fill to combine data as well as split data.

Type in manually, the first ‘Product Code’ into the cell, by combining the Location Code and ID.
Press CTRL+Enter to stay in the same cell.
From the Data tab, in the Data Tools group, click Flash Fill.
Or, press the keyboard shortcut CTRL+E.

*Use Flash Fill to combine data in Excel*

Let’s Wrap Up

In this guide, we saw how to split data in Excel using Excel text functions. We also covered how to combine data in Excel using the CONCAT and ampersand methods. We hope, you find this guide helpful.

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

How to Split and Combine Data in Excel?

Splitting and Combining Data in Excel – An Overview