I was presented with a problem when I was attempted to print address labels. I had an Excel file that had 200 people with their name, address, city, state, and zip-code all in different columns. In order to save time, I wanted to use Excel functions to concatenate all of the columns into one field, which sounds easy enough. I used Excel’s built in CONCATENATE() function.

Address Columns in Excel

The formula looked similar to the following:

This combined all of the columns, however the fields were placed all on one line.

Concatenate columns in Excel

Upon searching for a solution I found several forums that indicated that I should use CHAR(10) to program a soft return. So, I added CHAR(10) to my formula where I needed a line break.

My formula now looked similar to the following:

Here were my results:

Soft Return in Excel 2013

As you can see, there were no changes to my address label cell. What the majority of the forums failed to mention is that CHAR(10) will only work if Text Wrap is enabled for that field.

  1. Highlight the entire Address Label column
    Highlighted Cells in Excel 2013
  2. Right click on the highlighted area and select Format Cells…
    Format Cells in Excel 2013
  3. Navigate to the Alignment tab
  4. Under the Text control heading check the Wrap Text checkbox
  5. Click the OK button to save your settings
    Cell Alignment - Wrap Text in Excel 2013

Your address label is now formatted with the correct line breaks!

Concatenate Address Fields in Excel 2013

Please note that if you are using MAC you will have to enter CHAR(13) instead of CHAR(10).

 

Like this post? Share it!