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

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.
   

1. 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and select the cell with the 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.

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.
   

1. 2. Add a semicolon, followed by a comma and a space between quotation marks.

1. 3. Repeat the previous step for each address line, as shown below.

1. 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. 1. Type the CONCAT function and select the cell with the first string.
   

1. 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and type the TEXT function.

1. 3. Select the cell with the number, followed by a semicolon. Between quotation marks, type the number format you want to apply.

1. 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. 1. Type the CONCAT function and select the cell with the first string.

1. 2. Add a semicolon, followed by a space between quotation marks. Add another semicolon and type the TEXT function.

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.

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.

1. 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. 1. Type the result you want for the first row.
   

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

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

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

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.

• How to Combine Multiple Excel Columns Into One?
• How to Use CONCATENATE in Google Sheets (Complete Guide + Examples)
• How to Merge Cells in Google Sheets (Complete Guide)
• TEXTJOIN in Google Sheets (Easy Guide)