When you need to concatenate or join the contents of cells in Microsoft Excel, you have a few options depending on your needs. In this guide, you will learn about the CONCATENATE function in Microsoft Excel and its replacement: CONCAT. You have step-by-step instructions on how to use the function to join strings separated by different delimiters, like a space or a comma.
You will also learn how to combine CONCAT with the TEXT function to maintain the format of numbers, dates, and times when concatenating them. Additionally, you will learn three more ways to concatenate in Microsoft Excel: the ampersand symbol (&), the TEXTJOIN function, and the flash fill feature. Finally, you will learn how to do the opposite of concatenating in Excel: splitting cell contents.
CONCAT & CONCATENATE Functions in Excel
While the CONCATENATE function is still available for compatibility with earlier versions of Excel, Microsoft’s support site recommends using the replacement: CONCAT. You can use Excel’s CONCAT function to join any values you type or the contents of cells, whether they are text, numbers, or dates. You can concatenate up to 254 items or a total of 32767 characters.
Unlike its predecessor, CONCAT can handle arrays. Unlike TEXTJOIN, however, it has no ‘delimiter’ parameter, so each instance has to be added separately. In other words, if you need to concatenate multiple items using delimiters, you should consider using TEXTJOIN.
How to Use the CONCAT Function in Excel?
Below, you have step-by-step instructions on how to use the CONCAT function to concatenate strings with different types of delimiters, as well as numbers and dates.
Join Strings with Delimiters using CONCAT
Follow the steps below to concatenate strings using different delimiters. If typed directly into the function, any string or character - including delimiters - must be enclosed in quotation marks
Concatenate Strings with Space Delimiter
Follow the steps below to concatenate strings using a space as the delimiter.
- 1. Type the CONCAT function and select the cell with the first string.
- 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and select the cell with the second string.
- 3. Add the closing parenthesis and press ‘Enter’ to see the result. Double-click the fill handle to copy the formula down to the last row.
Concatenate Strings with Commas
You can delimit items using any character or string you want. For example, you can join the different lines in an address with a comma and a space to get the full address.
- 1. Type the CONCAT function and select the cell with the 1st address line.
- 2. Add a semicolon, followed by a comma and a space between quotation marks.
- 3. Repeat the previous step for each address line, as shown below.
- 4. Add the closing parenthesis and press ‘Enter’ to see the result. Double-click the fill handle to copy the formula down.
CONCAT with TEXT to Format Numbers or Dates
If the numbers or dates you want to concatenate are formatted as text, they will be reproduced as they are. However, if they are formatted as numbers or dates, the format is lost when you use CONCAT. To avoid this, use TEXT to apply the format to the result of CONCAT.
Concatenate and Format Numbers
For example, if you use CONCAT to join a string with a number formatted as currency, the format is lost in the process.
Instead, wrap the TEXT function around the number and add the required format between quotation marks, as shown below.
- 1. Type the CONCAT function and select the cell with the first string.
- 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and type the TEXT function.
- 3. Select the cell with the number, followed by a semicolon. Between quotation marks, type the number format you want to apply.
- 4. Close both parentheses and press ‘Enter’ to see the result.
Concatenate and Format Dates & Times
If the date or time is properly formatted as such, the format will be lost when you concatenate it. For example, the result of functions like DATE, TODAY, or NOW is formatted as a date or date & time. To preserve the format, use the TEXT function, as shown below.
- 1. Type the CONCAT function and select the cell with the first string.
- 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and type the TEXT function.
- 3. Select the cell with the date, followed by a semicolon. Between quotation marks, type the date format you want to apply and close the parenthesis.
- 4. Add a semicolon followed by a space in quotation marks. After another semicolon, add another TEXT function and repeat the previous step for the cell with the time.
- 5. Close both parentheses and press ‘Enter’ to see the result.
3 More Ways to Concatenate in Excel
In addition to the CONCAT or CONCATENATE functions, there are other ways to concatenate items in Microsoft Excel. You can use the ampersand symbol (&) between items or cell references to concatenate them. However, if you need to concatenate multiple items using a delimiter, TEXTJOIN is the function you want.
Not only does it have a ‘delimiter’ parameter, but it also allows you to ignore empty cells, so you don’t have to skip them when selecting the cells. Finally, if you want to concatenate the items in one column with the corresponding item in another column, you can do the first examples yourself and use flash fill to recognize and repeat the pattern.
Concatenate Using the Ampersand (&)
To use this method, type the equal sign and select the first item followed by the ampersand symbol (&). Continue adding items separated by ampersands. If you want to use delimiters, you also need to add them as items.
Concatenate Using TEXTJOIN
The TEXTJOIN function is ideal when joining multiple items with delimiters, as it will save you a lot of typing. Additionally, you can ignore empty cells in your selection using the second parameter: ‘ignore_empty’.
Concatenate Using Flash Fill
Finally, you can use the flash fill feature to complete the pattern based on a couple of examples. However, keep in mind that this will break the link to the original data, so future changes will not be reflected in the Concatenate version.
- 1. Type the result you want for the first row.
- 2. Grab the fill handle and drag it down to the last row. Click the button in the bottom right corner and select ‘Flash Fill’.
- 3. As you can see, the pattern has been applied correctly.
Opposite of CONCATENATE in Excel: TEXTSPLIT
If you need to split strings, instead of concatenating them, you can use the TEXTSPLIT function. To learn more ways to split cells in Excel, check out our guide on How to Split Cells in Excel: 6 Easy Methods.
Conclusion
You now know about the CONCATENATE function and its improved replacement: CONCAT. You know how to use it to concatenate strings using delimiters, as well as how to combine it with the TEXT function to maintain the format of numbers, dates, and times. You also know three other methods for concatenating items in Microsoft Excel: the ampersand symbol (&), the TEXTJOIN function, and the flash fill feature. Finally, you also know about a function that does the opposite of CONCAT or TEXTJOIN: the TEXTSPLIT function.
Check out the guides below to learn more about joining or splitting items in Google Sheets and Microsoft Excel.