Excel provides a wide range of functions to enhance your spreadsheet and simplify your work. One such function is concatenate in Excel.

What is concatenated in Excel 

In Excel, concatenating cells join the contents of the selected cells together. In simpler words, concatenate in Excel refers to the joining of two or more values. This technique is frequently used to join a few text passages that are located in separate cells (referred to as text strings or just strings) or to insert a value that was computed using a formula in the middle of a text passage.

The Concatenate function allows the addition of up to 30 text items and returns them in text format. 

The formula is as follows

=concatenate(text1, text2, text3…..)

use of formula

How to Combine data using the concatenate function 

  1. Select the cell where you want the combined data to be placed 
  2. Type =concatenate( 
  3. Select the formula
  4. Select the date you want to be places 
  5. Add commas to separate the cells you are merging 
  6. Close the formulae with parenthesis and press enter. Eg: 

           =concatenate( A2, “family”) 

Concatenate values of several cells 

 A simple formula is used to combine the cells. For eg to combine 2 cells like B1 and B2, the following formula can be used:

=concatenate(B1, B2)

The text will be combined without space 

Thus to add space the following formula should be used 

=concatenate(B1,” ”, B2)

Concatenate a Text String and a Cell Value

There are more uses to concatenate in Excel function than just combining cell values. The function can also be used to join a string and a cell value. This can be done by putting the data in parentheses.  As an example:

=conatetenate(B1,” ”, B2, “ ”, “henry harvin”) 

Concatenate a text value and formula calculated value 

Using concatenate in Excel, a text value and calculated value can also be combined in a cell 

To understand better, here is a formula to return the current date:

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

Concatenate columns in Excel

To join two or more columns, you need to enter your concatenation formula in the first cell, and then copy it down to other cells by dragging the fill handle. 

For example, to combine two columns A and B the values with a space, the formula in C2 copied down is:



= A2 & ” ” & B2

Combine data using ampersand symbol (&) 

Ampersand (&) operation can also be used to combine data without using any functions. 

This is a much easier way to merge data. 

To combine data you can follow these steps

  1. Select the cell in which you wish to combine the data 
  2. Type the equal to sign (=) 
  3. Select the first cell you want to combine 
  4. Use “ ” to add space 
  5. Then add ampersand (&) to combine further cells ]
  6. Press the enter key to see the text 
  7. For eg =A1&” ”&A2

How to combine text and keep the numbers formatted

When using concatenate in Excel and combining a text string with a number, percentage, or date, you might want to keep the original formatting of a numeric value differently. This can be done by using the format code inside the TEXT function. This can be put in a concatenation formula.

Here are a few formula examples that combine text and numbers:

  • Number with 2 decimal places and the $ sign:

=A2 & ” ” & TEXT(B2, “$#,#0.00”)

  • Number without insignificant zeros and the $ sign:

=A2 & ” ” & TEXT(B2, “0.#”)

  • Fractional number:

=A2 & ” ” & TEXT(B2, “# ?/???”)

  • To concatenate text and percentage, the formulas are:

Percent with two decimal places:

=A12 & ” ” & TEXT(B12, “0.00%”)

  • Rounded whole percent:

=A12 & ” ” & TEXT(B12, “0%”)

Concatenate in Excel with Merge Cells add-in

You can efficiently work with the Merge Cells add-in included in Ultimate Suite for Excel to:

  • You can Merge several cells without losing data.
  • Concatenate the values of several cells into a single cell and separate them with any delimiter of your choice
  • The Merge Cells tool works with all Excel versions from 2016 to 365 and can combine all data types including text strings, numbers, dates, and special symbols. Its two key advantages are simplicity and speed – any concatenation is done in a couple of clicks.

Important Points to Remember

Here are some important points to remember while using concatenate in Excel. 

  • At least one text argument should be present to make the concatenate function work. 
  • If the values of the concatenate arguments are invalid, the formula returns a #VALUE! Error.
  • The result of the concatenate in Excel is always a text string, even when all the source values are numbers.

Recommended courses 

Excel has become an essential part of professional life. It is an added skill that differentiates you from other candidates while applying for a job. Learning and enhancing your Excel skills is thus important, especially advanced Excel and its functions. 

We recommend Henry Harvin’s Advanced excel course which will give you an understanding of how to use Excel properly and thus make your work easier using its functions. 

Some details are listed below 

  • Total Program Fee ₹ 7500
  •  Learning Period: 24 Hours
  • Get Exposure to 11+ projects 
  • Learn to Apply Advanced Formulas, Perform Data Analysis and Data Visualization, and Create Pivot Tables and dashboards
  • Live Online Classroom Core and Brush-up Training Sessions


Excel skills have been prominent for a while now. It is a skill that has hence become a necessity for today’s generation. Knowing Excel functions especially advanced tools helps in enhancing the efficiency of the work. It helps in saving time, analyzing data, and hence completing tasks. 

It is used in a variety of streams for example in business, it is used in project management, performance reporting, accounting management, operations management, business evaluation, etc. 

Concatenate in Excel is used to combine the data in the cells, it is a text function in advanced Excel to simply the presentation of text and thus enhance the functioning of your spreadsheets. 

To conclude, in this blog, we learned how to use concatenate in Excel, various ways to use it, and using the ampersand. 


Q1 What is concatenate in Excel 

A1 It refers to combining two or more text cells using the text formula =concatenate(text1, text2,….) 

Q2 How to add space or commas in concatenate 

A2 commas and space can be added to the function by simply using the parentheses in the formula. For eg: =concatenate(A2,” ”, A3, ”,”, A4)

Q3 What do advanced Excel courses teach 

A3 Advanced Excel course teaches how to efficiently make use of Excel functions to make your work easier and enhance your skills for technical work 

Q4 What type of function is concatenate in Excel 

A4 is a text function that allows combination of a string of texts or values within one cell 

Q5 How much time it takes to learn this skill 

A5 Usually these courses take up to one month to complete, however, they need regular practice and brushing up to make use of all the functions properly. 

Author Bio

My name is Rupanshi Goel, I am currently pursuing B.Com from Delhi University. I have had a keen interest in writing since my school times and have been passionate about the same. Writing for Henry Harvin helped me enhance my writing skills and enabled me to learn more about this field. 

Join the Discussion

Interested in Henry Harvin Blog?
Get Course Membership Worth Rs 6000/-
For Free

Our Career Advisor will give you a call shortly

Someone from India

Just purchased a course

1 minutes ago
Henry Harvin Student's Reviews
Henry Harvin Reviews on Trustpilot | Henry Harvin Reviews on Ambitionbox |
Henry Harvin Reviews on Glassdoor| Henry Harvin Reviews on Coursereport