- Insert two new columns into the spreadsheet after the column with the second address line. These will be columns F and G in the example.
- Highlight cell F2 (the first address line)
- Select Insert, function from the menu bar
NOTE: In Office 2007, the 'Insert Function' button is under the Formulas tab across the top
- Select All as the Function Category and Concatenate as the Function Name
- In the Text 1 field of the Concatenate window, type D2 (cell containing address line 1)
- In the Text 2 field, type /n
- In the Text 3 field, type E2 (cell containing address line 2)
- Click OK
- Copy the function from cell F2 all the way down column F
NOTE: This should be done only for records that have a second address line. If all addresses do not have a second address line, first, sort the spreadsheet by column E (e.g. the column with the second address line). Those records with two address lines appears at the top of the spreadsheet. When copying the function, copy it to those records with a second address line.
- Highlight cells F2 through Fx where Fx is the last cell in column F with information
- Select Edit, Copy from the menu bar
- Click in cell G2 (the second blank column added in step #1)
- Select Edit, Paste Special from the menu bar
- In the Paste section of the Paste Special window, mark Values
- Click OK
- Delete columns D, E and F (address line 1, address line 2, and the original formula)
- Type AddrLines as the header for the new column D (the final combined address lines column)
- Select File, Save from the menu bar
- Give the file a descriptive name and select CSV (Comma-delimited) as the format
NOTE: Even though the carriage return will not reformat the concatenated cell, it will import correctly.