Excel has multiple functionalities that combine text from several cells into one big text. This is called ‘text concatenation’. In the examples below, we’ll assume our table includes the following columns: Street, Number, Postal Code and City.
& Operator
The ampersand is probably the most popular way to concatenate text. This works perfectly for a limited number of cells and keeps the formula fairly short.
=[@Street] & ” ” & [@Number] & ” ” & [@Postal Code] & ” ” & [@City]
The CONCATENATEfunction
Many people who don’t know the & operator use this function. When you enter the function, you’ll see an orange exclamation mark next to it. This means that it’s actually a little outdated and better alternatives are available. Still, the function does what it’s supposed to do:
=CONCATENATE([@Street];” “;[@Number];” “;[@Postal Code];” “;[@City])
The CONCATfunction
This function also joins the content of several cells into one big whole, but unlike the previous options, it lets you select several cells at once. With the previous options, you needed to select each cell separately.
The disadvantage of this function is that you can’t tell Excel whether the values should be separated from each other with one or more characters.
The TEXTJOIN function
This Excel function is fairly new and very useful. It combines the advantages of the CONCAT function (the possibility to select a range of cells) with the advantage of the & operator and the CONCATENATE function (the possibility to add a separator).
This function also has another advantage: it skips empty cells. Imagine your table includes rows where the number and postal code are left blank. If you use one of the other options, you’ll need extra functions to avoid ending up with multiple spaces one after the other (e.g.: Trim, If…).
If you use TEXTJOIN, you can choose whether or not you want to skip these blank cells. If you don’t enter anything, they’ll be skipped by default.
Do you want to delve deeper into Excel’s functions? Be sure to register for one of our Excel trainings.
