If you’ve ever struggled with joining or combining data into a single cell or splitting it across multiple cells in Microsoft Excel, you’re in the right place. This guide will take you through both processes using two key Excel functions: TEXTJOIN and TEXTSPLIT.

Whether you’re managing employee records or organizing address data, Excel makes it easier than you might think. Read on, and by the end, you’ll be able to master these functions and streamline your workflow.

Combining Data into One Cell with TEXTJOIN

Let’s begin with combining data from multiple cells into one. Imagine you have a list of employee first and last names stored in separate cells, and you want to join them into a single cell, separated by a space.

Steps for Joining Data with a Delimiter:

  1. Select the Cell: Click on the cell where you want to combine the text.
  2. Use the TEXTJOIN Function: In the formula bar, type: =TEXTJOIN(" ", TRUE, A2, B2)
    • Here, " " sets the delimiter as a space (you could use a comma, dash, or another delimiter if needed).
    • The second argument, TRUE, tells Excel to ignore any empty cells.
    • A2 and B2 are the cells holding the first and last names that you want to join.
  3. Press Enter: Excel will combine the contents of those cells, putting the first and last names together with a space in between.

Now, if you want this applied across a whole column, just double-click the bottom right corner of the cell you just filled. Excel will automatically apply the function to the entire column.

RELATED TOPIC: How to Create a Pivot Table from Multiple Workbooks in Excel

How to Keep the Combined Data:

Once you’ve joined the data, you may want to keep it in a different place in Excel without the function attached. Here’s how to do that:

  1. Copy the cells with the formula.
  2. Right-click where you want to paste the final data.
  3. Select “Paste Values” to paste only the data, not the formula.

Splitting Data from One Cell into Multiple Cells with TEXTSPLIT

Next, let’s look at how to split data from one cell into several cells in Excel. This is useful when you receive data with multiple pieces of information—like an address—that’s all jammed into one cell but separated by commas.

For example, let’s say your address data looks like this:

“123 Fake St, Springfield, IL, 62701”

And you want to split the data into separate columns in Excel for Street, City, State, and Zip Code.

Steps for Splitting Data with a Delimiter:

  1. Select the Cell: Click the cell where you want the split Data to start.
  2. Use the TEXTSPLIT Function: In the formula bar, type: =TEXTSPLIT(D2, ",")
    • D2 is the cell with the Data to be split.
    • "," is the delimiter in this case, which is a comma.
  3. Press Enter: The address will be split into separate columns for Street, City, State, and Zip Code.

Unlike the TEXTJOIN function, you can’t double-click to fill cells down when using TEXTSPLIT. Instead, you’ll need to manually drag down the formula to apply it to other cells.

Save the Split Data:

Just like with TEXTJOIN, if you need the split data to stay put without the function, copy the output and paste it as values.

  1. Highlight the results.
  2. Right-click and select “Copy”.
  3. Choose “Paste Values” to remove the formula and leave only the separated data.

RELATED TOPIC: How to Use Text to Columns in Microsoft Excel

Wrapping It Up

And that’s it—you now know how to join or split data in Microsoft Excel! From combining first and last names into one cell to separating address details, these simple functions will save you time.

If you found this guide helpful, check out the video above for a step-by-step walkthrough, and feel free to like, comment, or share with others. Be sure to subscribe for more Excel tips to boost your skills.

Got any questions or other Excel topics you’d like to learn about? Drop a comment below!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR