Data Import & Export

How to Add Missing Spaces Between Names and Addresses in Excel

Elise Williams Sep 25, 2026 869 views

Question details

The user needs to bulk add missing spaces to combined names and addresses in a large Excel list to improve readability.

How to Add Missing Spaces Between Names and Addresses in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up imported or improperly formatted data where spaces between names and street addresses have been removed.
Observed behavior: Hundreds of entries are merged together without spaces, making the data difficult to read, filter, or process.

Before you start

Before attempting bulk data cleaning, ensure your data has a somewhat consistent pattern (like capital letters indicating a new word or a number starting an address), and replace sensitive personal information with dummy data if sharing the file online for help.

Solution 1Recommended

Use Flash Fill to Automatically Add Spaces

Flash Fill is the fastest and most efficient way to split glued text by recognizing the manual pattern you type.

Excel's Flash Fill feature uses artificial intelligence to detect patterns in your data entry. By manually correcting the first row, you train Excel on where the spaces should be placed.

Insert a blank column

Right-click the column header immediately to the right of your combined name and address data, and select 'Insert' to create a blank column.

Type the corrected format manually

In the first cell of the new blank column, manually type the name and address exactly as it should appear, with all the proper spaces inserted, and press Enter.

Trigger Flash Fill

Select the empty cell directly below your typed entry. Press the keyboard shortcut Ctrl + E, or go to the Data tab on the ribbon and click 'Flash Fill'. Excel will automatically populate the remaining rows based on your pattern.

Use Flash Fill to Automatically Add Spaces

Pattern Recognition: Flash Fill works best when the data follows a recognizable structure. If some rows fail, correct them manually; Excel will update its pattern recognition and adjust the rest.

Solution 2

Separate Text Using Power Query

If names are formatted in CamelCase or the address starts with a number, Power Query can cleanly split the columns.

Efficient Data Cleaning

Clean and Format Your Spreadsheets Easily with WPS Office

WPS Spreadsheet provides powerful and intuitive data cleaning tools like Flash Fill, Text to Columns, and an extensive library of text formulas to quickly separate merged names and addresses without complex coding.

1. Open your file in WPS: Launch WPS Spreadsheet and open the document containing your merged name and address data.
2. Use Flash Fill: Type the correctly spaced name and address format in the adjacent column, select the next empty cell, and press Ctrl+E.
3. Save your cleaned data: Review the automatically generated spaces to ensure accuracy, then save your file seamlessly as an .xlsx document.

100% compatible with Microsoft Excel (.xlsx, .xls) file formats.Built-in Flash Fill to automatically recognize and apply text formatting patterns.Lightweight architecture ensures it runs smoothly even with massive data lists.Free to download and use with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't Flash Fill working accurately for all my names and addresses?

Flash Fill relies on recognizable patterns. If your data lacks consistent structure—such as erratic capitalization, varying middle names, or addresses without street numbers—Excel might not accurately guess where to insert spaces. Manually correcting a few errors often helps train the tool.

Is there a formula to automatically add a space before every capital letter?

Standard Excel functions do not offer a simple single formula for this exact scenario. However, in newer versions of Excel, you can use the REGEXREPLACE function to target uppercase letters, or alternatively use a custom VBA macro to insert spaces before capital letters.

Can I use Text to Columns to add these missing spaces?

Text to Columns is generally used to split data into separate columns rather than adding spaces within a single cell. However, you can use Text to Columns with Fixed Widths to split the data if character counts are identical across rows, and then use the CONCAT or TEXTJOIN function to combine them back with spaces.

Excel & Data Problems