“To unite or merge” is what “concatenate” means. Excel concatenate function merges text from many cells into a single cell. The concatenate function in Excel concatenates (joins) up to 30 items and displays the result as text. Cell references and hard-coded text strings can be used to provide values. CONCATENATE is categorized under text functions in Excel.
Syntax =CONCATENATE (text1, text2, [text3], …)
The Excel concatenate function uses the following arguments:
- The first item to be connected. A cell reference, a text value, or a number can be used as the item.
- Users can add any additional text elements they like. Users can link up to 255 items and up to 8192 characters.
Concatenating the values of multiple cells:
The following is a straightforward formula for CONCATENATING the values of two cells, A2 and B2.
=CONCATENATE(A2, B2)
Without delimiters, the values will be merged.
To separate the values with a space, use “ “.
Example =CONCATENATE(A3, “ “, B3)
Concatenating a cell value and a text string:
The concatenate function in Excel isn’t just for merging cell values. The function may also connect a cell value with a string. For example:
=CONCATENATE(A2, ” “, B2, ” WRITE ANYTHING“)
Concatenating a text string and formula calculated value:
A text string and a formula-calculated value can also be concatenated.
For example, users can use the following formula to return the current date:
=CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy“))
Concatenate cells with space, comma, and other characters:
Users may need to link the values in the worksheet using commas, spaces, or other punctuation marks. To do so, use the syntax listed below.
Concatenating two cells with space =CONCATENATE(A2, “ “, B2)
Concatenating two cells with a comma =CONCATENATE(A3, “, ”, B3)
Concatenating two cells with space =CONCATENATE(A4, “ – “, B4)
Excel concatenate function for columns:
It’s pretty simple to concatenate the entire column at once. Type the formula in the first cell, then drag the fill handle down to the other cells to replicate it (the little square that appears at the bottom right corner of the selected cells).
How to unconcatenate in Excel?
There are multiple ways to reverse the Concatenate function. Reverse the concatenate Excel function to split cell value into various cells with the text to columns method.
- First, select the cells to split.
- Then, click Data and go to Text to Columns.
- In the first window of the Convert Text to Columns Wizard, choose the Delimited option.
- After that, click the Next button in the next window of the wizard, and mention the separator that users want to use under the Delimiters option.
- Then click the Next button in the last window of the wizard, select General under the Column data format, and choose a cell where users want to locate the split values.
- Click on the Finish button. As a result, the selected cell values have been split into multiple columns.
Conclusion:
So, how to concatenate text in an Excel spreadsheet is explained above. There are multiple distinct syntax examples for the Excel concatenate function. The concat Excel function, which superseded the CONCATENATE feature, was launched in Excel 2019.
