Function Problems

How to Combine Split Address Fields in Excel Without Losing Data

Amos Gikunda Oct 23, 2026 869 views

Question details

The user needs to merge address components that are currently split across multiple columns into a single unified cell without losing data or messing up the formatting.

How to Combine Address Fields in Excel Without Losing Data

Product: Microsoft Excel
Device & OS: not provided
Scenario: Exporting records from a customer management system where addresses are split into separate fields, requiring a bulk merge for over 4,000 rows.
Observed behavior: Manually cutting and pasting separate address parts into a single cell is highly time-consuming, tedious, and prone to formatting errors, especially given variable lines and blank fields.

Before you start

Before applying any formulas, ensure you have standardized your column headers (e.g., Address Line 1, Address Line 2, City, State, ZIP) to make selecting your data ranges easier.

Solution 1Recommended

Use the TEXTJOIN Function to Combine Address Columns

TEXTJOIN is the most robust method for merging addresses because it allows you to specify a delimiter (like a comma or space) and automatically ignores blank cells, accommodating variable-length addresses perfectly.

This function is highly recommended for CRM data exports because some customers may have an 'Address Line 2' while others do not. TEXTJOIN skips the blanks seamlessly so you do not end up with awkward double commas.

Select an output cell

Click on an empty cell in a new column where you want the combined, full address to appear.

Enter the TEXTJOIN formula

Type =TEXTJOIN(", ", TRUE, A2:E2) assuming your address parts are in columns A through E. The TRUE argument ensures any blank fields in the row are ignored.

Use CHAR(10) for line breaks

If you prefer line breaks instead of commas, use CHAR(10) as the delimiter: =TEXTJOIN(CHAR(10), TRUE, A2:E2).

Apply to all rows

Press Enter to apply the formula, then double-click the small square at the bottom-right corner of the cell to auto-fill the formula down to all 4,000+ rows.

Use the TEXTJOIN Function to Combine Address Columns

Enable Wrap Text: If you use CHAR(10) for line breaks, ensure you click 'Wrap Text' in the Home tab so the address displays properly on multiple lines within the cell.

Solution 2

Combine Address Data Quickly Using Flash Fill

Flash Fill is a smart tool that learns your typing pattern and automatically fills the rest of the column. It is great for relatively clean data without too many complex variations.

Solution 3

Merge Addresses Using the Ampersand (&) Symbol

If you are using an older version of Excel that does not support TEXTJOIN, you can manually link cells and insert text strings using the ampersand symbol.

Efficient Data Management with WPS

Combine Large Address Datasets Instantly in WPS Spreadsheet

WPS Spreadsheet is a highly efficient tool for handling large datasets exported from CRM systems. It fully supports advanced functions like TEXTJOIN, CONCAT, and smart Flash Fill, allowing you to merge thousands of split address records in seconds without lagging or freezing.

1. Open your dataset: Open your downloaded CSV or Excel address file in WPS Spreadsheet.
2. Select a blank cell: Click on an empty column cell next to your split data.
3. Apply TEXTJOIN: Type =TEXTJOIN(", ", TRUE, A2:E2) to instantly merge the address parts while intelligently ignoring any blank columns.
4. Auto-fill: Double-click the bottom-right corner fill handle of the cell to rapidly auto-fill the formula down for all your customers.

Fully compatible with Microsoft Excel formulas like TEXTJOIN and CONCATENATE.Lightning-fast processing for large datasets with 4,000+ rows.Built-in smart Flash Fill for codeless data combination.Free and lightweight alternative to heavy spreadsheet applications.

microsoft office alternative - wps office

Frequently Asked Questions

How do I add a line break between street and city when merging addresses?

You can use the CHAR(10) function to insert a carriage return within your formula. For example, =A2 & CHAR(10) & B2. After applying the formula, select the cell and click the 'Wrap Text' button on the Home ribbon to make the line break visible.

Why are there extra commas when some address lines are blank?

If you use the basic CONCATENATE function or the ampersand (&), it will insert commas even if the referenced cell is empty. To prevent this, use the TEXTJOIN function and set its second argument to TRUE, which commands the spreadsheet to ignore completely empty cells.

Can I convert the combined formula results into regular text?

Yes. Once you have combined all your addresses using a formula, select the entire column of results, press Ctrl + C to copy, then right-click the same selection and choose 'Paste as Values'. This removes the underlying formulas and leaves only the static text.

Is there a way to automate address merging using Power Query?

Yes, Power Query is excellent for automated data shaping. Go to Data > From Table/Range. In the Power Query Editor, hold Ctrl and select your address columns in order, right-click the header, and choose 'Merge Columns'. Select your desired delimiter and click OK, then Load it back to your sheet.

Excel & Data Problems