4 Ways to Merge Text in Excel

After you have A spreadsheet full of data You may realize that you want to incorporate some text, such as first and last names, cities and states, or something similar. This guide shows you some easy ways to incorporate text into Excel So you can choose the best method for your data.

Use Flash Fill to blend text.

Flash Fill feature allows you to: Excel Create a pattern, then quickly fill other cells with that pattern. Smarter than AutoFill, Flash Fill can sense what you're trying to do and adjust accordingly. It doesn't involve formulas like the methods below. If you're not fond of working with formulas or functions, this option is for you.

1. Go to cell Where you want to place the combined text for the first pieces of data. This is usually the cell near the beginning of the column or row.
   
2. Type data As you want to read them all together once and press Enter Or Return. For example, we merge the first name in the cell A2 and the last name in cell B2 and enter the way we want to display it in the cell C2.
   
3. Locate Cell with embedded text In our example, this is the cell. C2. Go to the tab Home page , and click on the drop-down menu. Filling In the Edit section, choose Flash Fill
   
4. The remaining cells will be filled in the same pattern as the first cell.

Enable and use autofill with Flash

Excel provides a way to automatically fill cells with the Flash Fill feature that can save you a few clicks.

1. To enable this option, go to “File -> Options -> Advanced.” check box Fast autofill In the Edit Options section, you may need to select the Original box. Enable autocomplete for cell values Above first.
   
2. Locate "OK" To save the change, go back to the sheet where you want to merge the text.
   
3. Go to the first cell where you want the combined text, and type it as you want it displayed. Press Enter Or Return Start typing the second set of combined data. You'll see a preview of how you'll fill in the property. Flash Fill Remaining data.
   
4. Click on Enter Or Return To accept the preview and fill the cells.
   

Use Ampersand factor

Another way to combine text in Excel is to use the ampersand (&) operator to drag the formula down to fill the remaining cells.

1. Locate cell Where you want the aggregated data and type the formula below, replacing the cell references with your own:

   = A2 & B2

2. You should see the data from each cell merged into the cell that contains the formula.
   
3. If you want to insert a space between words, use the following formula instead. Make sure to put a space between the quotation marks.

   =A2&" "&B2

4. This formula takes the text in the cell. A2 , adds a space, and ends the text in the cell. B2. The text with a space between the text from the two cells will be merged.
   
5. To fill the remaining cells with the same formula, select Filling handle (square) In the lower-right corner of the cell containing the formula.
   
6. Drag down to fill the remaining 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 text strings, the methods above can be cumbersome. Instead, use the TEXTJOIN function in Excel. This function is available in newer versions of Excel, including Microsoft 365, Excel for the web, Excel 2021, and Excel 2019.

The formula of the formula is TEXTJOIN (delimiter, ignore_empty, text1, text2,…) , where the first three arguments are required.

• Determinant: The separator you want to use between the combined text, such as a space, comma, or hyphen.
• Ignore_empty: Use TRUE to ignore blank cells or FALSE to include blank cells separated by the delimiter.
• Text 1: The cell or cell range that contains the text. You can also include text in a formula as an argument.

Let's look at an example:

1. We collect City names separated by commas, ignoring empty cells. The data we want to combine is in the cells from A2 to A6. We use this formula:

   =TEXTJOIN(", ",TRUE,A2:A6)

2. To split the formula, we have a comma and a space between the quotes, TRUE for the “ignore_empty” argument, and the cell range A2 to A6.
   
3. In another example, we combine the words in cells A1 through A6, leaving a space between each one, and include the empty cells using this formula:

   =TEXTJOIN(", ",FALSE,A1:A6)

4. You can see that cell A4 is empty and insert that empty into the result between "or" و "to me".
   

Use the CONCAT function

Another function that can help you consolidate data is CONCAT. This function is a newer version of CONCATENATE, which was replaced in Excel 2016, Excel for the web, and the Excel mobile app. CONCATENATE is still available for backward compatibility, but it may become unavailable at some point.

The formula syntax is CONCAT(Text1, Text2, …), where only the first argument is required, although you may want to combine more than one piece of data. You can include Up to 253 text items And specified between quotes between the arguments.

In a simple example, we combine the text in cells A2 and B2 with a comma between them, using this formula:

=CONCAT(A2,",",B2)

In this example, we combine the two words. "Welcome" و "Home" With a space between the two words. See below for the formula:

=CONCAT("Welcome","home")

Note that each word and the space between each word is within quotation marks.

This last example uses the function so you can see its flexibility. We combine our customer's last name (A2), a space, the order number (B2), a hyphen, and the site ID 123. Here's 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 down into the column using the “fill handle” as described earlier.

Frequently Asked Questions

Q1: Can I simply merge cells to merge text in Excel?
The answer: At Merge cells Each cell contains data, Excel keeps only the data in the leftmost or top-left cell and discards the rest. You'll need to use one of the methods above to merge text in Excel.

Q2: Aren't conjunctions and concat the same thing?
The answer: It's true that these two methods work essentially the same. The only obvious difference is that the CONCAT function has a limit of 253 strings, while the ampersand has no limit. If you're choosing between the two, use whichever is more convenient for you.

Q3: What is the opposite of TEXTJOIN in Excel?
The answer: The TEXTSPLIT function performs the opposite action of TEXTJOIN. TEXTSPLIT allows you to split a text string into individual cells, similar to Excel's Text-to-Columns feature.