How to Use Concatenate in Excel?

Table of Contents

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…..)

How to Combine data using the concatenate function

Select the cell where you want the combined data to be placed
Type =concatenate(
Select the formula
Select the date you want to be places
Add commas to separate the cells you are merging
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:

=CONCATENATE(A2, ” “, B2)

= 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

Select the cell in which you wish to combine the data
Type the equal to sign (=)
Select the first cell you want to combine
Use “ ” to add space
Then add ampersand (&) to combine further cells ]
Press the enter key to see the text
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

Conclusion

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.

FAQs

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.