Buy Now

The Importance Of Concatenate Function In Excel For Beginners

excel functions Apr 29, 2023

Do long text strings overwhelm you and leave you feeling lost?

Well, despair no longer! The CONCATENATE function can help you quickly and easily put all your bits of information into one neat little package. This powerful tool, Microsoft Excel function will simplify your data management.

Read on as we cover the following:

  • What is Excel CONCATENATE Function?

  • Syntax of Concatenate in Excel

  • Step-by-Step Guide for CONCATENATE Function

  • Final Thoughts on CONCATENATE Function In Excel

  • Frequently Asked Questions on CONCATENATE Function In Excel

Read Also: How to Combine Cells in Excel

What is Excel CONCATENATE Function?

The Excel Concatenate function combines the text of two or more cells into one cell. It lets you combine data from multiple cells together, whether they contain numbers or text, without affecting the cell references.

CONCATENATE was renamed to the CONCAT function after the release of Excel 2016. If you use a newer version of Excel, you can use either CONCATENATE or CONCAT. For older versions of Excel, you need to use CONCATENATE.

Syntax of Concatenate in Excel

The Concatenate Syntax in Excel:

Concatenate function.

The text parameter can be a text string, cell reference, or formula that produces a value.

Notes for using CONCATENATE formula in Excel.

To ensure that your CONCATENATE formulas yield accurate results, please remember the following :

  • Once a CONCATENATE function's arguments return a #VALUE! ERROR, it is invalid.

  • If you want to combine data in Excel, use the CONCATENATE or CONCAT formula. If you add special characters like &, put them inside double quotation marks (" & ").

  • You can only use 255 strings or 8192 letters in a CONCATENATE or CONCAT formula.

  • The formula needs at least one string to work.

Read more: Google Sheets Countif: Everything You Need To Know

Step-by-Step Guide for CONCATENATE Function

Follow the step-by-step guide below to use the CONCATENATE function in Excel.

  1. To begin, open your Excel worksheet and choose an empty cell. Then, click on the formula bar at the top of the screen to enter your new CONCAT or CONCATENATE formula.Type the Concatenate function in a formula bar.

  1. Type the CONCATENATE or Concat formula in the formula bar.Concatenate function.

  1. Select your text string from different columns. Column A for the first name and Column B for the middle initial.Select the date for the Concatenate function.

Note: the two double quotation marks with space will create a separation between the value of A2 and b2.

To use the following formula:

  1. Press the Enter key to combine text or CONCATENATE values.Press the Enter key to finish the Concatenate formula.


When you use CONCATENATE or CONCAT in Excel, the answer it gives you is a text. Sometimes this can change numbers like dates in the wrong way.

To stop this from happening, use the TEXT function so that it looks right when CONCATENATE or CONCAT changes the date.

Use the following formula below to concatenate dates:

=CONCATENATE("Today's date is  ", TEXT(TODAY(), "mm/dd/yyyy"))
=CONCAT("Jelo was born in ",TEXT("06/04/2001","mm/dd/yy"))

Concatenate function with dates.

Using the Text and CONCATENATE functions will grant you to view numbers displayed in the date format you want.

CONCATENATE Special Characters

In Excel, special characters like ampersands and hyphens can stop a CONCATENATE or CONCAT formula from working. You can fix this by typing the formula in quotation marks.

Open an Excel spreadsheet and select an empty cell. Then, type a CONCATENATE or CONCAT formula with your data using the formula bar.

=CONCATENATE(A42," & ",B42)
=CONCAT("I want to go at the beach"," & ","have fun")

Concatenate function. with special characters.

It would be best to use CONCATENATE or CONCAT formulas to make it easier to read the output, which will also help keep words from sticking together.

Read Also: Basic Google Sheets Functions: What are They and How to Use Them

Final Thoughts on CONCATENATE Function In Excel

The Concatenate function is a valuable tool that makes combining text data easier while keeping all the individual pieces intact. With this feature, you can produce pages of data and information in seconds, saving you much time and energy.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the Related Articles section of this blog post.

Subscribe to Simple Sheets on Youtube for the most straightforward Excel video tutorials!

Frequently Asked Questions on CONCATENATE Function In Excel

Can I use an array like A1:A4 to Concatenate a range of cells?

To Concatenate data range cells:

  1. Select a cell to put your formula.

  2. Type the CONCATENATE Formula.

  3. Hold the Control or CTRL Key, then select the cells you want to Concatenate.

  4. After selecting the cells, stop holding the CTRL key, then press the Enter key.

Can I use an array like A1:A4 to Concatenate a range of cells?

You must select the cells manually for the CONCATENATE formula work like (A1, A2, A3, A4).

Why isn't the CONCATENATE function working?

When using the CONCATENATE function, you have to list each argument separately. Put a comma between each one. If you use any other symbol or list them all together, the function won't work.

Related Articles:

How to Combine Cells in Excel

How to Add Leading Zeros in Microsoft Excel

The Remainder Formula Excel: Everything You Need to Know

Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons

We hate SPAM. We will never sell your information, for any reason.