Function Problems

How to Combine Address Columns into One Excel Cell Using TEXTJOIN

Phi Hung Vo Oct 9, 2026 869 views

Question details

The user needs to combine address data currently spread across multiple columns (Address 1, Address 2, Address 3, Postcode) into a single, well-formatted cell.

How to Combine Address Columns into One Excel Cell Using TEXTJOIN

Product: Excel
Device & OS: not provided
Scenario: Merging and formatting separated address fields into a single mailing block within a spreadsheet.
Observed behavior: The address components are isolated in separate columns. Previous attempts to use the REPT function were unsuccessful in correctly combining the fields into a single cell with line breaks.

Before you start

Verify that your version of Excel or spreadsheet software supports the TEXTJOIN function, which is available in Microsoft 365, Excel 2019, and newer versions including WPS Office.

Solution 1Recommended

Use TEXTJOIN with CHAR(10) to Combine Address Fields

Using the TEXTJOIN function is the most efficient method to merge multiple address columns because it lets you specify a line break delimiter and automatically ignores blank cells.

The TEXTJOIN function requires three main arguments: a delimiter, a TRUE/FALSE setting to ignore empty cells, and the data range. By using CHAR(10) as the delimiter, each piece of the address is placed on a new line within the same cell.

Select the destination cell

Click on the cell where you want the combined address block to appear.

Enter the TEXTJOIN formula

Type the formula =TEXTJOIN(CHAR(10), TRUE, D2:G2) and press Enter. Adjust the range D2:G2 if your address columns are located elsewhere.

Enable Wrap Text

Select the cell with your new formula, navigate to the Home tab on the ribbon, and click the 'Wrap Text' button so the line breaks display properly.

Apply to the remaining rows

Click and drag the fill handle at the bottom-right corner of the cell down the column to apply the formula to the rest of your address list.

Use TEXTJOIN with CHAR(10) to Combine Address Fields

Wrap Text is Required: The CHAR(10) line break will not visibly separate the text onto multiple lines unless 'Wrap Text' is turned on for the result cell.

Solution 2

Use the Ampersand (&) Operator for Older Versions

If you are using an older version of Excel that lacks the TEXTJOIN function, you can manually combine the columns using the ampersand operator combined with IF statements.

Data Management Made Easy

Effortlessly Combine Address Columns in WPS Spreadsheet

WPS Spreadsheet fully supports modern data functions like TEXTJOIN, allowing you to merge and format complex datasets quickly. Clean up your address lists and manage your contacts effectively in a familiar, intuitive interface.

1. Open your address list: Launch WPS Spreadsheet and open the document containing your separated address columns.
2. Insert the TEXTJOIN formula: Type =TEXTJOIN(CHAR(10), TRUE, D2:G2) into the cell where you want the full address to appear.
3. Format the text: Select the cell, navigate to the Home tab, and click the 'Wrap Text' icon to reveal the line breaks.
4. Fill the column: Drag the fill handle down to instantly merge the addresses for all rows in your dataset.

Fully supports TEXTJOIN, CONCATENATE, and other advanced data functions.Seamless compatibility with Microsoft Excel formulas and .xlsx file formats.Free, lightweight, and easy to use across Windows, Mac, and mobile devices.

Frequently Asked Questions

Why are my combined addresses showing up on a single line despite using CHAR(10)?

This happens when cell formatting prevents line breaks from displaying. To fix this, select the cell with your formula, navigate to the Home tab, and click the 'Wrap Text' button.

What happens if one of the address columns is blank?

When using the TEXTJOIN function, setting the second argument to TRUE tells Excel to automatically ignore empty cells. This ensures you do not get extra blank lines or duplicate delimiters in the combined address.

Is TEXTJOIN available in all versions of Excel?

No, the TEXTJOIN function was introduced in Excel 2019 and Microsoft 365. If you are using Excel 2016 or older, you will need to use alternative methods like the CONCATENATE function or the ampersand (&) operator.

Can I use a comma instead of a line break to separate address parts?

Yes. To separate the address fields with a comma and a space, simply replace CHAR(10) with ", " in your formula. It should look like this: =TEXTJOIN(", ", TRUE, D2:G2).

Excel & Data Problems