Just before Christmas I was approached by someone who wanted to create name badges for a conference that they were running in February. She had an Excel spreadsheet containing the name and location of each conference attendee (it was an internal conference) and wanted to combine this information into a single cell – as per the screenshot below:
The formula that I initially suggested for column D was: =A1 & ” ” & B1 & CHAR(10) & C1
- The first part of the formula joins the contents of cell A1 to a space to the contents of B1
- The CHAR function returns a character based on the number inside the brackets/parentheses. 10 represents a line feed/new line
- The final part of the formula places the contents of C1 (the conference attendee’s location) on a new line within the cell
Once the formula has been entered, you must turn on Text Wrapping for the cell/cells that contain the formulas (column D in this example). To do this, select the cell/cells and click the WRAP TEXT button on the home tab of The Ribbon.
At this point, D1 contained Bob Barrow North
A fair bit of head scratching went on until my customer told me that she was using Excel on a Mac. The Mac operating system uses Character 13 to represent a line feed, so we changed the formula to =A1 & ” ” & B1 & CHAR(13) & C1
In fact, to make sure that the formula would work on either Windows or Mac, rather than maintaining 2 copies of the file, the formula was changed to =A1 & ” ” & B1 & CHAR(10) & CHAR(13) & C1