This tutorial will explain excel concatenate functions along with how to utilize concatenation in numbers, text string dates and “&” operator. We will also include formulas combine with columns, single cells, and a range of cells.
More than that, we’ll explore different techniques in excel concatenation, where you can choose methods suited to your worksheets.
In working on our worksheets, we usually have data that is not structured according to our needs. As a result, we split the content or combine cells and columns into one column.
What is Concatenate in Excel
The Concatenate function in excel joins and combines the word. Apparently, it is under the category of Text functions. So this function is often used to combine text from different cells.
Furthermore, financial analysts are the ones who encounter this function when they deal with financial analysis. Whereas the data is not always structured for analysis, and we often need to combine data from one or more cells into one cell or split data from one cell into different cells.
Basically, there are two ways that help you combine data in your Excel workbook.
- Concatenate Cell Values
- Merge Cells
So, when concatenating cell values, you only combine the contents of the cells. Simply, it’s the process that joins two or more cell values together.
However, merging cells physically joins or combines two or more cells into a single cell. Hence you have one larger cell that is displayed across multiple rows or columns.
To clearly understand, we will examine syntax and argument of this function.
Syntax
=CONCATENATE(text1, text2, [text3], …)
Arguments
- text1 – The first text arguments value to join together.
- text2 – The second text value to join together.
- text3 – [optional] The third text value to join together.
Return value
Combined text together.
How to Concatenate in Excel
Here are the simplest ways to use Concat function in your worksheet:
- Select the cell where you want the combined data to be placed.
- Enter the formula =CONCAT(.
- Select the first cell you want to combine.
- Add commas, spaces, or text. Always put quotation marks to separate the cells you are joining.
- Enter the formula within parentheses. =CONCAT(A2, ” Timothy”).
- Press enter to see the output.
Concatenating the Values of Several Cells
The simple formulas to CONCATENATE the values of several cells are as follows:
For instance, we combine the firstname and last names of students (cells a2 and b2):
- =CONCATENATE(A2,B2)
The values will be merged without any spaces. Use “” in separating values.
- =CONCATENATE(A2, “ “, B2)
Concatenating a Text String and a Cell Value
The Excel CONCATENATE function is not only limited to combining cell values.
Apart from this, use the function to join cell value and string. For example:
- =CONCATENATE(A2, ” “, B2, ” SIMPLILEARN”)
Concatenate Cells with Comma, Space and Other Characters
In working your worksheet, it might necessary to join values which may add a space, commas, or various punctuation marks. To do that, follow the syntax given below.
Two cells with space concatenate:
- =CONCATENATE(A2, “ “ ,B2)
Concatenating two cells with a comma:
- =CONCATENATE(A3, “, ”, B3)
Concatenating two cells with space:
- =CONCATENATE(A4, “ – “, B4)
“&” operator to concatenate strings in Excel
The ampersand sign (&) in Microsoft Excel is another way to concatenate cells. Moreover it is an flexible operator in every certainties, besides using ampersand character is much easier to type than word concatenate.
For example, to concatenate two cell values in column cell A1 and b2 with a space in-between, the formula is:
Combine text and numbers keeping formatting
This time there are instances where you want to keep the formatting of numeric values when concatenating a text string, percentage or date number. So this can be accomplished by using or applying TEXT function inside the format which can be put in the formula.
The following are example formulas that joined text and numbers:
| Formula | Result |
| Number with 2 decimal places: =I2 & ” ” & TEXT(H2, “#,#0.00”) |  |
| Number without insignificant zeros: =I3 & ” ” & TEXT(H2, “0.00%”) |  |
| Fractional number: =A2 & ” ” & TEXT(H2, “# ?/???”) |  |
| Concatenating text and percentage. Percent with two decimal places: =A4 & ” ” & TEXT(H2, “0.00%”) |  |
| Rounded whole percent: =A2& ” ” & TEXT(H2, “0%”) |  |
| Concatenating a Text String and Formula Calculated Value =CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yyyy”)) |  |
What is Excel Concatenate Opposite
Splitting one cell’s contents into multiple cells is the opposite of concatenate function. This can be done in a few different ways:
- Flash Fill in Excel 2016 and higher
- TEXTSPLIT function in Excel 365
- Text to Columns feature
- Split cells custom formula (RIGHT, MID, LEFT, etc.)
Conclusion
So this article has discussed the Excel concatenation together with the following:
- Concatenate function joining 30 characters or text jointly.
- Text items can be text strings, numbers, or cell references that refer to one cell.
- Text items can be cell references, text strings, or numbers that direct on the other cell.
- The alternative of concatenation is the ampersand character(&). Hence the output is still the same, however using ampersand is more flexible, short and easy-to-read formula.
- Numbers are converted to text when joined. See the TEXT function if you need to specify a number format for a number being joined.
