Remove extra spaces
Location on ribbon: Tab DVD Addin → Group Text and Number → Menu Text ops
Command ID: btnRemoveSpaces
What it does
The command applies Excel's TRIM function to every text cell in the selection: leading and trailing spaces are cut, and runs of spaces inside the string collapse to a single space. Data pasted from PDFs, from estimating software, or typed by several people usually carries this noise.
Stray spaces are the classic reason VLOOKUP, SUMIF and PivotTables fail to match a work-item code even though the two strings look identical on screen. Running this before any lookup removes most of those cases.
How to use
- Select the range to clean — a whole column is fine.
- Go to tab DVD Addin → group Text and Number → menu Text ops → Remove extra spaces.
- It runs immediately, with no dialog. The Excel status bar reports how many cells were fixed.
- If nothing is selected, the command shows "Please select a data range" and stops.
Example
A work-item column copied from a subcontractor's estimate, pasted into Excel with padding everywhere.
| Before | After |
|---|---|
Footing concrete M250 | Footing concrete M250 |
Rebar D≤10 | Rebar D≤10 |
Column formwork | Column formwork |
After the clean-up, the VLOOKUP between the BoQ sheet and the estimate sheet matches again.
Notes
- Ctrl+Z cannot undo this.
- Only text cells are affected; numbers, dates and blanks are left alone.
- A formula returning text is overwritten with its static value, so avoid selecting formula cells.
- The command does not handle special whitespace such as NBSP (character 160) or zero-width characters that come with web copies. Use Smart Data Cleaning for those.
- Cells that already have no extra spaces are not rewritten, so large ranges still process quickly.
Related
- Smart Data Cleaning… — deeper clean-up: NBSP, casing, dates, duplicate rows.
- Auto format — once spaces are gone, convert numeric/date strings to real data types.
- Remove accents — strip Vietnamese diacritics from the same text-ops menu.
- Bulk find / replace… — clear leftover odd characters across many sheets.