How to Remove Spaces from an Excel Column Using VBA (Keeping Headers)
Question details
The user needs a VBA macro to delete all spaces in a specific Excel column, starting from row 2 to avoid altering the header, and handling stubborn non-breaking spaces.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up data copied from a website in a specific column using a VBA script without affecting the first row.
- Observed behavior
- Standard replace methods might accidentally target the header row or fail entirely if the spaces are non-breaking HTML spaces.
Before running any VBA macro, ensure you have enabled the Developer tab in your spreadsheet and identified the exact column number (e.g., column 5 for column E) that contains your data.
Use Range.Replace for Normal and Non-Breaking Spaces
This is the most robust method for cleaning data copied from the web, as it targets only the data rows and handles stubborn HTML spaces.
When data is copied from the internet, spaces often appear as non-breaking spaces (ASCII character 160). Standard space replacement looks for ASCII 32 and will fail to remove these web spaces. Using VBA's Replace method targeting Chr(160) resolves this.
Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications editor.
Click on 'Insert' in the top menu and select 'Module' to create a blank script window.
Write your code to start at row 2 and column 5. For example: Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))
Append the replace function to target non-breaking spaces: .Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
Press F5 or click the Run button to execute the script and clean the column.
Apply WorksheetFunction.Substitute Programmatically
If you prefer iterating through arrays or using Excel's built-in Substitute function programmatically, this method works well for standard spaces.
Clean Your Spreadsheet Data Effortlessly with WPS Office
WPS Spreadsheet offers robust VBA and Macro support, allowing you to run your space-removing scripts just as you would in Microsoft Excel. It's lightweight, fully compatible, and incredibly easy to use.
- 1. Open Your Spreadsheet: Launch WPS Office and open the workbook containing the data you need to clean.
- 2. Access the VBA Editor: Navigate to the Developer tab and click on 'Macros', or simply press ALT + F11 to open the VBA editor.
- 3. Insert Your Code: Paste your data-cleaning VBA code into a new module, specifying the starting row as 2.
- 4. Run the Macro: Click run to instantly remove all spaces from your targeted column while perfectly preserving your headers.
Frequently Asked Questions
Why aren't spaces being removed when I use the standard Replace function?
If your data was copied from a website or web-based CRM, the spaces are likely non-breaking spaces (ASCII 160). The standard replace tool only targets regular spaces (ASCII 32). You must use Chr(160) as the search value in your VBA replace method.
How do I ensure my header in row 1 is not affected by the VBA macro?
You need to define your VBA range to explicitly start at row 2. Instead of calling Columns("E").Replace, use Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp)) to only target data cells from row 2 downwards in column 5.
Can I use Excel's TRIM function in VBA instead?
The TRIM function only removes leading, trailing, and consecutive double spaces. If you need to remove *all* spaces within the text (including single spaces between words or characters), using the Substitute method or Replace with an empty string ("") is the correct approach.
