The data we collect in Excel spreadsheets are often unorganized and need to be restructured before using in reports or other calculations. When capturing first and last names, product IDs, or other properties, data collection tools can have different inputs for all information. You might want to split or merge this for better presentation.
The CONCATENATE function is one important tool in your arsenal for manipulating data. This function is used for merging multiple strings. It can take up 255 arguments to merge them in a single cell.
An alternative to this formula is using the ampersand character (&) between each item. However the CONCATENATE function will be shorter and easier to read if you are joining several strings. Let’s take a look at how this function works. You can download our sample workbook from the link below.
Syntax
=CONCATENATE(text1, [text2], ...)
Arguments
text1: The text to join
text2: (optional) Additional text to join. (max. of 255 items, 8,192 characters)
Examples
Combining the First and Last Names
Let’s assume that you have name information and the first and last names are in different cells or columns. You can use the CONCATENATE function to merge them and even add styling to the end product.
To combine the first and last names in [last name], [first name] format use this formula,
=CONCATENATE(C3,", ",B3)
This will combine the surname (i.e. C3) and name (i.e. B3), adding a comma-space separator (, ) in between.
To apply this formula to the rest of the column, copy it down through the rest of the fields by dragging the bottom-right corner towards the last cell.
Combining text in consecutive cells
If you have multiple cells in a single dimensional range (1 row – many columns, or many rows – 1 column) you can use this method to avoid combining them manually.
Begin by creating a helper column or row anywhere on your spreadsheet. The first cell of the helper column (or row) should be equal to the first item on the data range (i.e. =J3).
The second cell of the helper column (or row) will contain the main structure of our final string. For example, here we uses [text1] / [text2] / [text3] / ….. to achieve this structure. This way, we can get the previous item in the helper column (or row) and the adjacent cell from the original range.
=CONCATENATE(K3," / ",J4)
Final step is copying the formula all the way down, like we did before.
Notes
- If you need to combine only 2 text strings using an ampersand (&) is a faster method.
=A1&A2
- A common mistake is omitting the quotes when using a text string. This will give the #NAME! error.
=CONCATENATE("I will return an",error)
- Numbers do not need to be inside quotation marks.
=CONCATENATE("This is a number: ",1)
- Excel 2016 introduced the CONCAT function as a successor to the CONCATENATE. This formula works essentially the same way.
CONCATENATE is essentially the glue to combining data into a single cell. While the ampersand method might seem easier, joining multiple strings is much more efficient with the CONCATENATE function.
