Code numbers such as invoice or product codes are common in Excel. When you work with codes like these you often need to join multiple pieces of text (or ‘strings’) together, sometimes with a symbol between them such as a hyphen or +.
This example was sent to us from one of our customers:
She wanted to concatenate the three codes together with + symbols between them, resulting in codes like R+TG+340.
Using the concatenation operator (&)
The ampersand (&) is Excel’s ‘concatenation’ operator. You can use it in Excel formulas to join pieces of text together.
In the example shown above, the formula =A2&B2 would result in RTG.
We want + symbols to be shown between each of the codes, so the correct formula is:
Concatenation is covered in much greater depth in our Expert Skills Books and E-books.
Using the new TEXTJOIN function
TEXTJOIN is a new function that has been added to Excel 2016 via Office 365. If you don’t have an Office 365 subscription you will not be able to use the TEXTJOIN function.
TEXTJOIN does exactly the same thing as the concatenation formula, but makes the formula much shorter and easier to write:
Remember that TEXTJOIN will not be usable by anybody who bought the retail ‘perpetual’ version of Excel 2016. Only users of ‘Excel 365’ can use TEXTJOIN at present, but it will be included in Excel 2019 when it is released.
If you’re unfamiliar with Excel formulas and functions, you could greatly benefit from our free Basic Skills E-book, which will teach you the basics of Excel formulas.
You can download a copy of the sample workbook to see the formulas in action.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.