A common example of a situation that requires concatenation is assembling a mailing address from data in separate columns. If I want to create a mailing address using this data, I need to create a formula that uses CONCATENATION to bring the name, street, city, state and zip together. In addition to cell references, I also need to include “literal text” for spaces and a comma. This works, but notice that everything just ends up on the same line. Enabling text wrapping isn’t going to fix the problem because the line breaks will be random depending on the column width. To fix this, I need to insert actual line breaks, and this is where the CHAR function is useful. On Windows, character 10 is a line break and on the Mac, it’s character 13. To add line breaks, I just need to use the CHAR function with the appropriate number. In this case, because I’m working on a Mac, I need to use CHAR(13). Once I add the character formula where I need the line breaks, the address will display correctly. Note that you must have text wrapping enabled when you use line breaks. This works fine, but it’s a hassle to string together all these references with literal text. One trick you can use to speed up the process, and reduce errors, is to define any literal text as a “constant” using a named range. To start off, I’ll set up cells for a space, a comma, and a line break. Next, I add the required text in each cell using CHAR(13) for the line break, as before. Finally, I’ll name these cells “space,” “comma,” and “break” using Excel’s named range feature. Now when I create a formula for concatenation, I can just click to include both cell references and punctuation. I don’t need to worry about adding double quotes because Excel already understands that the space, comma, and line breaks are text and, as a bonus, the formula is much easier to read and debug.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
