After you have a spreadsheet full of data, you may realize that you want to combine some of the text, like first and last names, cities and states, or something similar. This guide shows you a few easy ways to combine text in Excel so that you can choose the best method for your data.
Content
- Use Flash Fill to Combine Text
- Enable and Use Automatic Flash Fill
- Use the Ampersand Operator
- Use the TEXTJOIN Function
- Use the CONCAT Function
- Frequently Asked Questions
Use Flash Fill to Combine Text
The Flash Fill feature in Excel lets you create a pattern, then quickly fill other cells using that pattern. Smarter than AutoFill, Flash Fill can sense what you are trying to do and adjust accordingly. It doesn’t involve formulas like the methods listed below. If you’re not fond of working with formulas or functions, this option is for you.
- Go to the cell where you want to place the combined text for the first pieces of data. This is typically the cell near the start of the column or row.
- Type the data as you would like it to read once combined and press Enter or Return. For example, we are combining the first name in cell A2 and last name in cell B2 and entering the way we want it displayed in cell C2.
- Select the cell with the combined text; in our example, this is cell C2. Go to the “Home” tab, click the “Fill” drop-down menu in the Editing section, and choose “Flash Fill.”
- The remaining cells will populate with the same pattern as the first cell.
Enable and Use Automatic Flash Fill
Excel provides a way to automatically fill cells with the Flash Fill feature that can save you a few clicks.
- To enable this option, go to “File -> Options -> Advanced.” Check the box for “Automatically Flash Fill” in the Editing Options section. You may need to check the “Enable AutoComplete for cell values” parent box above first.
- Select “OK” to save your change, then return to the sheet where you want to combine the text.
- Go to the first cell where you want the combined text, and type it as you want it displayed. Press Enter or Return and start typing the second set of combined data. You will see a preview of the way Flash Fill will populate the remaining data.
- Press Enter or Return to accept the preview and fill the cells.
Use the Ampersand Operator
Another way to combine text in Excel is to use the ampersand operator (&) to drag the formula down to fill the remaining cells.
- Select the cell where you want the combined data and type the formula below, replacing the cell references with your own:
=A2&B2
- You should see the data from each cell combined into the cell that contains the formula.
- If you want to insert a space between the words, use the following formula instead. Be sure to place a space between the quotation marks.
=A2&" "&B2
- This formula takes the text in cell A2, adds a space, and finishes with the text in cell B2. Your text will be combined with a space between the text from the two cells.
- To fill the remaining cells with the same formula, select the “Fill Handle” (square) in the bottom right corner of the cell containing the formula.
- Drag down to populate the rest of the cells with the same formula. Excel automatically updates the formula with the correct cell references.
Use the TEXTJOIN Function
If you want to combine longer strings of text, it can be cumbersome to use the above methods. Instead, use Excel’s TEXTJOIN function. This function is available in newer versions of Excel, including Microsoft 365, Excel for the Web, Excel 2021, and Excel 2019.
The syntax for the formula is TEXTJOIN(delimiter, ignore_empty, text1, text2,…), where the first three arguments are required.
- Delimiter: the separator you want to use between the combined text, such as a space, comma, or hyphen.
- Ignore_empty: use TRUE to ignore empty cells or FALSE to include the blank cells separated by the delimiter.
- Text1: The cell or cell range containing the text. You can also insert the text into the formula as the argument.
Let’s look at an example:
- We are combining city names separated by commas, ignoring empty cells. The data we want to combine is in cells A2 through A6. We are using this formula:
=TEXTJOIN(", ",TRUE,A2:A6)
- To break down the formula, we have our comma and a space within quotation marks, TRUE for the “ignore_empty” argument, and the cell range A2 through A6.
- In another example, we are combining the words in cells A1 through A6 with a space between each and including blank cells using this formula:
=TEXTJOIN(", ",FALSE,A1:A6)
- You can see that cell A4 is empty and inserts that blank in our result between “or” and “to be.”
Use the CONCAT Function
Another function that can help you combine data is CONCAT. This function is a newer version of the CONCATENATE function, which was replaced in Excel 2016, Excel for the Web, and in the Excel mobile app. CONCATENATE is still available for backward compatibility but may become unavailable at some point.
The syntax for the formula is CONCAT(text1, text2,…), where only the first argument is required, although you’ll likely want to join more than one piece of data. You can include up to 253 text items and a delimiter in quotation marks between the arguments.
In a simple example, we are combining the text in cells A2 and B2 with a comma between, using this formula:
=CONCAT(A2,",",B2)
In this example, we are combining the words “Welcome” and “home” with a space between the words. See below for the formula:
=CONCAT("Welcome"," ","home")
Notice that each word and space between each word is within quotation marks.
This final example uses the function so that you can see its flexibility. We are combining the last name of our customer (A2), a space, our order number (B2), a hyphen, and the location ID 123. Below is the formula:
=CONCAT(A2," ",B2,"-",123)
As you can see, the CONCAT function can combine different types of strings, and you can copy and paste the formula downward in your column using the “Fill Handle” as described earlier.
Frequently Asked Questions
Can I simply merge cells to combine text in Excel?
When you merge cells that each contain data, Excel only retains the data in the far-left or upper-left cell and discards the rest. You’ll need to use one of the above methods to combine text in Excel.
Aren’t the ampersand and CONCAT methods the same thing?
It’s true that these two methods work basically the same way. The only apparent difference is that the CONCAT function has a 253-string limit, while using the ampersand has no limit. If you’re deciding between the two, use whichever you are most comfortable with.
What is the opposite of TEXTJOIN in Excel?
The TEXTSPLIT function performs the opposite action of TEXTJOIN. TEXTSPLIT allows you to separate a string of text into individual cells, similar to the Text-to-Columns feature in Excel.
Image Credit: Pixabay. All screenshots by Sandy Writtenhouse.
With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.
Subscribe to our newsletter!
Our latest tutorials delivered straight to your inbox
Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe