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.

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).

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

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)

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.

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.
No Comments Yet