Excel How To Join or Combine Text (3 ways)

Welcome to this tutorial on combining text in Excel! In this post, we will explore three different ways to join or combine text values in Excel. Whether you want to use Flash Fill, concatenation, or the TEXTJOIN function, we will cover them all. So let’s get started!

Video

Step-by-step Tutorial

The objective of this tutorial is to provide a step-by-step guide on how to combine text values in Excel using three different methods: Flash Fill, concatenation, and TEXTJOIN.

Method 1: Using Flash Fill

Flash Fill will detect a pattern based on your sample and attempt to fill the detected pattern down. For example, we’d like to combine the first and last name into a single combined string.

1. Start by providing a sample text value, such as “Eric Betz” in the first cell.

2. Go to the Data tab, and look for the Flash Fill command.

3. Click Flash Fill (or use the shortcut Ctrl+E) and bam, Excel will automatically fill values based on the pattern:

Note: if you wanted to change the format, like doing last name comma first, you would provide the corresponding sample:

Then, Flash Fill it down.

While Flash Fill is great for static lists, using a formula may be a better option when the list will change because they are dynamic. Let’s check it out next.

Method 2: Using Concatenation

In summary, concatenation joins text segments into one big text string. Each segment is a function argument.

We will use the same basic list for this example.

The basic formula syntax is:

=CONCATENATE(B10,", ",C10)

And here are the details:

1. Start by entering an equals sign (=) in the cell where you want to combine text.

2. Enter the concatenation function CONCATENATE. Note: depending on your version of Excel, you may have the newer CONCAT function.

3. Point to the first cell you want to combine and add a comma.

4. If you want to include a separator, add another comma, and enclose the desired separator in double quotes.

5. Close the function with a closing parenthesis and hit Enter.

6. Fill down the formula to the remaining cells.

Rather than using a concatenation function, we can use the concatenation operator (&) if preferred. The basic syntax is:

=B10 & ", " & C10

Whether you use the concatenation operator (&) or a concatenation function is personal preference as they both accomplish the same thing.

Method 3: Using TEXTJOIN

The TEXTJOIN function is similar to concatenation, but it enables us to specify the delimiter once and then reference an entire range of cells. This function automatically inserts the delimiter between each of the cell values in the range. This is especially useful for ranges with many cells.

Here is the basic syntax:

=TEXTJOIN(", ", TRUE, B10:C10)

1. Enter an equals sign followed by TEXTJOIN

2. Add the desired delimiter (e.g., comma and space) in double quotes.

3. Choose whether to ignore empty cells.

4. Specify the range of cells you want to join.

5. Close the function with a closing parenthesis and hit Enter.

6. Fill down the formula to the remaining cells and bam:

Conclusion

In this tutorial, we explored three different methods for combining text values in Excel. Whether you prefer using Flash Fill, concatenation, or the TEXTJOIN function, each method can help you achieve the desired result. Now you can easily join text values in Excel and save time with your data manipulation tasks. If you have any other preferred methods, please share by posting a comment below!

Sample File

FAQs:

Q: Can I use Flash Fill to join multiple columns?

A: Yes, you can use Flash Fill to combine text values from multiple columns. Just provide a sample in the desired format, and the use the Flash Fill command button. Excel will generate the results for the remaining cells.

Q: Do I need to include separators when using concatenation?

A: No, separators are optional when using concatenation functions. You can choose to include separators like commas or spaces between the text values, or leave them out.

Q: Can I combine text from different sheets using the concatenation?

A: Yes, concatenation can be used to combine text from different sheets. Simply specify the cells by selecting them and Excel will insert the correct reference.

Q: Can I use Flash Fill with numbers or dates in Excel?
A: Yes, Flash Fill can be used with numbers and dates in Excel. It can detect patterns and automatically fill in values based on the provided samples.

Q: Is there a limit to the number of text values I can join using the TEXTJOIN function?
A: If the resulting string exceeds 32,767 characters (cell limit), the TEXTJOIN function will return an error.

Q: Are there any restrictions on the characters that can be used as separators in the CONCATENATE function?
A: No, there are no specific restrictions on the characters that can be used as separators in the CONCATENATE function. You can use any valid character or combination of characters as your desired separator.

Q: Can I combine text values vertically using Flash Fill?
A: No, Flash Fill is designed to operate on values horizontally, across a row. If you want to combine values vertically, you’ll need to use other methods such as concatenation.

Q: Is it possible to undo the Flash Fill operation if I’m not satisfied with the results?
A: Yes, if you’re not satisfied with the results of the Flash Fill operation, you can undo it by pressing Ctrl+Z or using the Undo button in the Excel toolbar. This will revert the cells back to their original values before the Flash Fill was applied.