How to Concatenate in Excel – Beginner’s Guide

Here’s a handy guide with multiple examples of one of the most important Excel functions needed for data formatting – The Concatenate in Excel

When using Excel, in many cases, the data may be structured in such a way that it does not fit your needs and objectives. This is where the Concatenate function comes in handy.

You can use this Excel Function to easily combine data across rows, columns, and cells. Concatenate means combining the data from the contents of two or more cells into a single cell. This function in Excel is often used to join data such as names, addresses, display time, dates, etc.

In this article, you will learn the different techniques to implement the Concatenate function of Excel so as to effectively achieve your data formatting goals.

What is Concatenate?

The word ‘Concatenate’ comes from the  Latin word ‘concatenare.’ ‘Con’ means “with” or “together,” while ‘catena’ means “chain. 

The concatenate function in Excel combines the contents of two or more cells into one cell without physically changing the size of the cell. It is often used to join pieces of text (called text strings or strings) from individual cells into a single cell. 

Difference between “Merge” and “Concatenate” in Excel

Excel enables you to combine data in two ways: Merge cells or Concatenate their values.

When you Merge cells, you are “physically” merging two or more cells into a single cell. As a result, you have one large cell that is displayed across multiple rows and/or columns in your worksheet.

However, when you concatenate cells in Excel, you are combining only the contents of those cells.

There are two essential ways to concatenate strings in Excel –

• By using the CONCATENATE function
• By using the Excel “&” operator.

“Also Read: How To Make A Pie Chart In Excel“

How to Concatenate in Excel

The Excel CONCATENATE function concatenates up to 250 items with almost 9000 characters and returns the result as text. In Excel 2019 and later, the ‘CONCAT’ and ‘TEXTJOIN’ functions are efficient and more flexible alternatives.

The Concatenate function starts with the equal sign, followed by the function name(Concatenate), an open parentheses bracket followed by the text. 

The syntax of CONCATENATE is as follows:

CONCATENATE(text1, [text2], …)

In the above formula, a text can be any of the following – 

• Text string
• Cell reference 
• Formula-based value

Note: After Excel 2016, the Concatenate function can be replaced with the CONCAT Function with the same syntax. The CONCATENATE function is supported in all versions of Excel, including Office 365, Excel 2019, and Excel 2007.

“&” operator to concatenate strings in Excel

In Microsoft Excel, the & operator (Ampersand) is another way to concatenate cells. This method comes in very handy in many scenarios because typing the ampersand sign (&) is much quicker than typing the word “concatenate”.

Like the CONCATENATE function, you can use “&” in Excel to combine different text strings, cell values, and results returned by other functions.

“&” operator vs. CONCATENATE function

Many users wonder which is more efficient to concatenate strings in Excel – the CONCATENATE function or the “&” operator. The only major difference between CONCATENATE and the “&” operator is the 255 strings limit of the Excel CONCATENATE function and no such limitations when using the ampersand operator.

Other than that, there is no difference between these two concatenation methods, nor is there any speed difference between the CONCATENATE and “&” formulas. Hence it can be concluded that the difference boils down to only comfort and ease of use.

“Also Read: How To Remove Page Break In Excel“

How to use the CONCATENATE function in Excel?

Below you will find several ways of using the CONCATENATE function in Excel.

1. Basic use of CONCATENATE and “ &” function

The simplest CONCATENATE formula to combine the values of cells A1 and B1 is as follows:

=CONCATENATE(A1, B1)

Example 1: Join first and last name

To join separate first names and last names together into a single full name, you can use concatenation. In the example shown below, the formula in cell C2 is:

=CONCATENATE(A2,“ ”, B2)

The result is displayed in Column C, which is the “Full Name.” This is nothing but the text in A2 joined to the text in B2 and separated by a space.

Note: When you use concatenation in a formula, be sure to enclose the text in double quotes (“”). However, do not enclose the ampersand (&) in quotes unless you want it to appear in the final result. In the above example, you can use –

=B5&” “&C5

Example 2: Combine email with a display name

To combine an email address with a display name, you can use concatenation with a generic  formula as shown below :

=name&” <“&email&”>”

In the example shown, the formula in C2, copied down, is: 

=B2&” <“&C2&”>”

In the worksheet below, column A contains a name, and column B contains an email address. The formula in column C uses the ampersand character (&) to join the name and email address together. The result is a text string that contains both the name and email address.

In this example, the ampersand operator (&) is used to concatenate the name, email, and angle brackets manually. The ampersand is an extremely flexible way to concatenate text strings because it can be used in a formula anywhere.

Both CONCATENATE and CONCAT can be used to solve the above example like this:

=CONCATENATE (B5,” <“,C5,”>”)
=CONCAT (B5,” <“,C5,”>”)

“Also Read: How To Remove Table Format In Excel“

2. Concatenate cells by including – a space, comma, and other characters

Many a time, you may often need to join values in a way that may include commas, spaces, or other characters such as a hyphen or slash. To do this, simply include the character you want in your concatenation formula and enclose that character in quotation marks, as demonstrated in the following examples.

Example 1: Concatenating two cells including a space

     =CONCATENATE(A2, ” “, B2)   or

       =A2 & ” ” & B2

Example 2: Concatenating two cells including a comma

   =CONCATENATE(A3, “, “, B3 “, “, C3, “, “,D3) or

   =A3 & “, ” & B3 & “, ” & C3 & “, ” & D3

Example 3. Concatenating two cells with a hyphen

=CONCATENATE (A1, “-“, B1) or

=A1 & “-” & B1

3. Concatenate a text string and cell value

The Excel CONCATENATE function can be used to concatenate various text strings to make the result more meaningful. For example:

The below formula informs the user that a certain project has commenced.

=A2″ “&B2 & “commenced”

Additionally, you can add a text string in the beginning or in the middle of your Concatenate formula as well. Here it may be noted that we have added a hyphen after the word “completed” to separate the concatenated text strings.

= “Completed”& “-“& A3” “&B3

4. Concatenate a text string and a formula-calculated value

To make the result a formula more understandable for your users, you can concatenate it with a text string that explains what the value actually is.

For example you can use the following formula to return the current date:

=CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy”))

Here is another example of concatenating the text string and calculating a certain field. 

Suppose you have the data of a few customers, and you need to enter certain comments for those customers, as given below.

The comment we need in column D is like, “The invoice of a certain $ value was due this Month”. 

To get such comments in column D, we need to follow the below steps. Go to cell D2 and Enter CONCATENATE function as below.

5. Concatenate text strings with line breaks

In some situations, you may need to separate the values with a line break. A common example is merging data from several columns into a single column like a Mailing address.

A special CHAR function is needed to indicate a line break to the concatenation formula:

• On Windows, use CHAR(10)
• On the Mac system, use CHAR(13)

Here is an example – 

We have the address in various columns (from A to F), and we are putting them together in column G by using the concatenation operator “&”. The merged values are separated with a comma (“, “), space (” “) and a line break CHAR(10):

=A2 & ” ” & B2 & CHAR(10) & C2 & CHAR(10) & D2 & “, ” & E2 & ” ” & F2

Note – When using line breaks to separate the concatenated values, you must have the “Wrap text” option enabled for the result to display correctly. To do this, open the Format Cells dialog, switch to the Alignment tab, and check the Wrap text box.

6. Concatenate columns in Excel

In order to concatenate two or more columns in Excel, you must enter the usual concatenation formula in the first cell and then copy it down to other cells by dragging the fill handle (the small square that appears in the lower right-hand corner of the selected cell).

For example, to concatenate two columns (column A and B) and separate the values with a space, you enter the following formula in cell C2 and then copy it down to other cells.

You can drag the fill handle ( which changes to a cross) to copy the formula, as shown in the screenshot below:

Note – A quick way to copy the formula down to other cells in the column is to select the cell with the formula and double-click the fill handle.

7. Concatenate a range of cells in Excel

The Excel CONCATENATE function does not accept arrays, and it’s not possible to refer to an entire range. Below you will find 3 methods to do range concatenation in Excel.

Method 1. Press CTRL to select multiple cells to be concatenated

To quickly select several cells, you can press the CTRL key and click on each cell you want to include in the CONCATENATE formula.

Select a cell where you want to enter the formula.

• Type ‘=CONCATENATE’ (in that cell or in the formula bar).
• Press and hold Ctrl and click on each cell you want to concatenate.
• Release the Ctrl button, type the closing parenthesis in the formula bar, and press Enter.

Note – When using this method, you must click each individual cell. If you select a range with the mouse, it results in an array being added to the formula, which the CONCATENATE function does not accept.

Method 2. Use the TRANSPOSE function to get the range

When you need to concatenate a huge range consisting of tens or hundreds of cells, the previous method is extremely tedious.

In such cases, a better way to combine text from different cells is by using the TRANSPOSE function to return an array and then replace it with individual cell references in a single click. Look at the below range of cells where you have a text, but every word is in a different cell, and you want to get it all in one cell.

In the cell where you want to output the concatenated range, enter the TRANSPOSE formula – 

        =TRANSPOSE(A1:A10)

In the formula bar, press F9 to replace the formula with calculated values. As a result, you will have an array of numbers to be concatenated. Delete the curly braces surrounding the array values.

Type =CONCATENATE( before the first value, then type the closing parenthesis after the last value and press Enter.

Note – Whichever method you use, the concatenated value in C1 is a text string (notice its left alignment in the cell), although each of the original values is a number. 

This is because the CONCATENATE function always returns a text string regardless of the source data type.

Method 3. Using the TextJoin for a range of cells

If you are using Excel 2016 (Office 365), there is a function called “TextJoin”. It can make it very easy for you to combine text from different cells into a single cell.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Where

• The space is a text string to use as a delimiter.
• If you want to ignore empty cells, set it as TRUE, and for do not ignore blank cells, set it as FALSE.
• Text1 and text2 to combine.

In the example below, you can combine the below list of values by using the formula:

=TEXTJOIN(” “,TRUE,A1:A5)

8. Concatenate dates in various formats

Method 1: When combining a text string and date, you have to use the TEXT function to display the date in the desired format.

For example:

=CONCATENATE (“Today is “, TEXT(TODAY(), “mm/dd/yy”))

or

=”Today is ” & TEXT (TODAY (), “mm/dd/yy”)

Imagine we have two fields in the dataset received from the client, namely “Year” and “Month”, both in numeric format. Our aim is to create a third field with a date format.

Example #1 – Combining Year and Month Fields to Form a Date Field

Click on the cell where you wish to create the new date and enter the formula below in the destination cell. You can use ‘-‘or ‘/’ as separators.

=B2&”-”&A2 or  =B4&”/”&A4

Example #2 – Combining Year and Month/Day Fields to Form a Date Field 

Click on the cell where you want to create the new date and enter the formula below in the selected cell. Drag the formula applied to cell C2 till cell C5; then we will get the results for the remaining data.

=CONCATENATE (B2&”/”&A2)

Method #2 – Using the Text () function in the formula to Concatenate and form a Date field

When combining a text string and date, you have to use the TEXT function to display the date in the desired format.

Example 1

Click on the cell where you want to create the new date and enter the formula below in the selected cell to get a date in a specified format.

=TEXT(B2&”/”&A2,”mmm-yyyy”)

Drag and copy to remaining cells.

Example 2

The data can also have separate fields specifying “Day”, “Month”, and “Year”.To combine and form a Date field, use the formula below.

=TEXT(C2&”/”&B2&”/”&A2,”dd-mmm-yyyy”)

Example 3

You can use the following formula to return the current date:

=CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy”))

Example 4: Concatenate Date and Time in Excel

Let us assume we want to record the date and time so that we can reference this during a monthly performance analysis. The result is a text string in Column D that provides a meaningful reference by sharing the date and time of the project status.

=CONCATENATE(A2,TEXT(B2,”dd/mm/yyyy”), “at, “ “, TEXT(C2,”hh:mm:ss”))

9. Concatenate Numbers in various formats

When concatenating numeric values like dates, times, percentages, etc., number formatting will be lost. For example, with the date 1-Jul-2021 in cell A1, the date reverts to a serial number during concatenation:

=CONCATENATE(“Date: “,A1) // returns “Date: 44378”

To apply formatting during concatenation, use the TEXT function:

=CONCATENATE(“The date is “,TEXT(A1,”dd/mm/yy”)) // “The date is 01/07/2021”.

When you concatenate a text string with a number or date, you may want to format the result differently based on your dataset. To do this, embed the TEXT function in your Excel concatenate formula.

The TEXT(value, format_text) function has two arguments:

• In the first argument (value), you supply a number or date to be converted to text or a reference to the cell containing a numeric value.
• In the second argument (format_text), you enter the desired format that the TEXT function can understand.

A few more formula examples that concatenate a text value and number are:

• =A2 & ” ” & TEXT (B2, “$#,#0.00”) – display the number with 2 decimal places and the $ sign.
• =A2 & ” ” & TEXT (B2, “0.#”) – does not display extra zeros and the $ sign.

Things to remember

To ensure that your CONCATENATE formulas always deliver the correct results, remember the following simple rules:

• Excel CONCATENATE function requires at least one “text” argument to work.
• In a single CONCATENATE formula, you can concatenate up to 255 strings, a total of 8,192 characters.
• The result of the CONCATENATE function is always a text string, even when all of the source values are numbers.
• Excel CONCATENATE does not recognize arrays. Each cell reference must be listed separately.
  For example, you should write =CONCATENATE(A1, A2, A3) instead of =CONCATENATE(A1:A3).
• If at least one of the CONCATENATE function’s arguments is invalid, the formula returns a #VALUE! Error.
• When concatenating values of different cells, pay careful attention to quotation marks and commas because they are very important for displaying the results properly.

The CONCATENATE function in Excel, along with its modern counterparts CONCAT and TEXTJOIN are extremely versatile and useful tools for combining text strings without impacting the rest of your worksheet.

How Lio can Help You

Lio is a great platform that can help entrepreneurs, homemakers, students, businessmen, managers, shop owners and many others. This mobile application helps to organize business data and present them in an eye-catching manner. 

Lio is a great platform for small business owners and can track a wholesome record of employee information for better employee management, customer data, etc. You can handle those data with ease.

If you want to be a professional, then you must save your time, you need to learn to arrange all the business strategies in one place. In that case, Lio can be your partner.

Entrepreneurs can also allow multiple authorized users of their office to access the information from various locations within minutes.

Lio is definitely for the win and using it for your business is only going to make your journey smooth and easy to track.

Step 1: Select the Language you want to work on. Lio on Android

Step 2: Create your account using your Phone Number or Email Id.

Verify the OTP and you are good to go.

Step 3: Select a template in which you want to add your data.

Add your Data with our Free Cloud Storage.

Step 4: All Done? Share and Collaborate with your contacts.