Don’t forget to share this post

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. 1. Type the CONCAT function and select the cell with the first string.
How to CONCATENATE in Excel Step By Step CONCAT First String
How to CONCATENATE in Excel (Step-By-Step) - CONCAT & First String
  1. 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and select the cell with the second string.
How to CONCATENATE in Excel Step By Step Delimiter Second String
How to CONCATENATE in Excel (Step-By-Step) - Delimiter & Second String
  1. 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.
How to CONCATENATE in Excel Step By Step Concatenated Result
How to CONCATENATE in Excel (Step-By-Step) - Concatenated Result

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. 1. Type the CONCAT function and select the cell with the 1st address line.
How to CONCATENATE in Excel Step By Step CONCAT Function
How to CONCATENATE in Excel (Step-By-Step) - CONCAT Function
  1. 2. Add a semicolon, followed by a comma and a space between quotation marks.
How to CONCATENATE in Excel Step By Step Delimiter
How to CONCATENATE in Excel (Step-By-Step) - Delimiter
  1. 3. Repeat the previous step for each address line, as shown below.
How to CONCATENATE in Excel Step By Step Repeat for Each Line
How to CONCATENATE in Excel (Step-By-Step) - Repeat for Each Line
  1. 4. Add the closing parenthesis and press ‘Enter’ to see the result. Double-click the fill handle to copy the formula down.
How to CONCATENATE in Excel Step By Step Concatenated Result 2
How to CONCATENATE in Excel (Step-By-Step) - Concatenated Result

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.

How to CONCATENATE in Excel Step By Step Currency Format Lost
How to CONCATENATE in Excel (Step-By-Step) - Currency Format Lost

Instead, wrap the TEXT function around the number and add the required format between quotation marks, as shown below.

  1. 1. Type the CONCAT function and select the cell with the first string.
How to CONCATENATE in Excel Step By Step CONCAT String
How to CONCATENATE in Excel (Step-By-Step) - CONCAT & String
  1. 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and type the TEXT function.
How to CONCATENATE in Excel Step By Step Delimiter TEXT
How to CONCATENATE in Excel (Step-By-Step) - Delimiter & TEXT
  1. 3. Select the cell with the number, followed by a semicolon. Between quotation marks, type the number format you want to apply.
How to CONCATENATE in Excel Step By Step Select Cell with Number Add Format
How to CONCATENATE in Excel (Step-By-Step) - Select Cell with Number & Add Format
  1. 4. Close both parentheses and press ‘Enter’ to see the result.
How to CONCATENATE in Excel Step By Step Concatenated Result 3
How to CONCATENATE in Excel (Step-By-Step) - Concatenated 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. 1. Type the CONCAT function and select the cell with the first string.
How to CONCATENATE in Excel Step By Step CONCAT String 2
How to CONCATENATE in Excel (Step-By-Step) - CONCAT & String
  1. 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and type the TEXT function.
How to CONCATENATE in Excel Step By Step Delimiter TEXT 2
How to CONCATENATE in Excel (Step-By-Step) - Delimiter & TEXT
  1. 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.
How to CONCATENATE in Excel Step By Step Select Cell with Date Add Format
How to CONCATENATE in Excel (Step-By-Step) - Select Cell with Date & Add Format
  1. 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.
How to CONCATENATE in Excel Step By Step Select Cell with Time Add Format
How to CONCATENATE in Excel (Step-By-Step) - Select Cell with Time & Add Format
  1. 5. Close both parentheses and press ‘Enter’ to see the result.
How to CONCATENATE in Excel Step By Step Concatenated Result 4
How to CONCATENATE in Excel (Step-By-Step) - Concatenated 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.

How to CONCATENATE in Excel Step By Step Join with Ampersand
How to CONCATENATE in Excel (Step-By-Step) - Join with Ampersand

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’.

How to CONCATENATE in Excel Step By Step Join with TEXTJOIN
How to CONCATENATE in Excel (Step-By-Step) - Join with TEXTJOIN

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. 1. Type the result you want for the first row.
How to CONCATENATE in Excel Step By Step Type Example
How to CONCATENATE in Excel (Step-By-Step) - Type Example
  1. 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’.
How to CONCATENATE in Excel Step By Step Drag Fill Handle
How to CONCATENATE in Excel (Step-By-Step) - Drag Fill Handle
  1. 3. As you can see, the pattern has been applied correctly.
How to CONCATENATE in Excel Step By Step Flash Fill Result
How to CONCATENATE in Excel (Step-By-Step) - Flash Fill Result

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.

How to CONCATENATE in Excel Step By Step TEXTSPLIT Function
How to CONCATENATE in Excel (Step-By-Step) - TEXTSPLIT Function

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Apr 11 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets