In this short post I will show you how to add a line break into the concatenate() function. It my example I will show you how to create something like an address label format.

We will start with a simple Excel spreadsheet shown below.

Excel example for concatenate() line break

In Line one I will show you the result if we do not use any special line break character in the concatenate() function. I used the function =CONCATENATE(A2,” “,B2,” “,C2,” “,D2,”, “,E2,” “,F2).

concatenate() function without the line break

The above function will create the result below which is not quite the result I am looking for.

concatenate() function resulte without the line break

I will now replace some of the spaces with the special character CHAR(10). The updated function will now look like =CONCATENATE(A3,” “,B3,CHAR(10),C3,CHAR(10),D3,”, “,E3,” “,F3)

concatenate() function with the line break special code

The special character CODE(10) will add a line break into the function so that you can return to a new line for the street and town lines. The result will look like the image below. Also note that you must click on the Wrap Text option for this cell or column.

concatenate() function with the line break special code result

There may be other methods to get this result but this one is relatively easy. I hope this helps some of my past, present and future students. Please post any questions or comments below. See more of my Excel Tips and Tricks.