Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

Clever concatenation with line breaks

Tags 
Summary 
In this video, we show you how to join different values together in one cell with line breaks. We also demo a clever way to add spaces, commas without double quotes.
Video Transcript 

In the real world, you often need to concatenate values in a way that includes line breaks and other punctuation. In this video, we'll look at a clever way to make this task easier and less error-prone.

A common example of a situation that requires the concatenation of multiple values 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 address, and the city state and zip code together. In addition to cell references, I also need 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 character function is useful.

On windows, character 10 is a line break and on the mac, it's character 13.

To add line breaks, I can use the CHAR function with the appropriate number. Since I'm working on the Mac right now, I need to use CHAR (13).

One I add this formula where I need line breaks, the address will display correctly. Note that you must have ext wrapping enabled when using 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 named ranges.

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 character 13 for the line break as before.

Finally, I'll name these cells "space", "comma", and "break".

Now when I create the formula for concatenation, I can just click to include both cell references and punctuation.

I don't need to worry about adding double quotes, and, as bonus, the formula is much easier to read and debug.

Author 
Dave Bruns

Related shortcuts