It is often very useful to join the contents of cells together into a text string, for instance combining forenames and surnames or putting explanatory text with a number (e.g "total sales in March were 10 million").
Three ways to join text
Excel provides three or four ways to join or CONCATENATE to form a text string in a cell:
- CONCATENATE or CONCAT in Excel 365
- use of the & symbol
All the examples include additional text (in this case a dash) as this is normally required to avoid the joined cells merging together. If the same separator is to be used the arguably the JOINTEXT method is the easiest. If on the other hand the additional text needs to be different then the & method or Concatenate method is the best. Personally I've always with the "&" method. Note the CONCAT function is new to Excel 365 and does exactly the same job as CONCATENATION (which remains for backward compatibility) but it is much easier to type!
You can also include other formulas into the expression which allows you to vary the contents of the resulting string based on IF statements etc.
Controlling the format of concatenated numbers
When pulling numbers into a concatenated string the result will display with no formatting applied to the number. This is especially annoying with dates which Excel reads as numbers. To avoid this it is possible to use the TEXT(n,"format") function as shown below.
Using this method you can control the format of any number inserted into a concatenation string (this doesn't work with the TEXTJOIN method). Note that this, sadly, does not allow you to change the colour or font of the inserted number. All such control is at the whole cell level rather than in the cell. For all possible options for formats which can be applied see this list from Microsoft or by inspecting the options when you look in the cell formatting dialogue.
Inserting line returns or quotes
Another trick is to include the CHAR(nnn) function where nnn is a number from 1-255 from the ASCII character set (see here for the complete list of the characters and their numbers). The most useful ones are:
- CHAR(10) - a line return or line feed. When the wrapping is switched on in the cell this adds a carriage return making a new line part of the way through your string.
- CHAR(34) - quote marks. This is especially useful as normally the presence of " means the end of a text element in the concatenation which can then break the formula. The only other way of inserting a " in a concatenation formula is to enter 4 quote marks ("""") which is all to easy to make a mistake doing. To avoid this simply add CHAR(34) and a " will result.
Note that Windows version of Excel us the ASCII code set however Macs use the Macintosh character set.