Delete Spaces

A

ajpowers

I've got spaces after data that's not supposed to be there, so VLOOKU
isn't working because it doesn't see the data as being the same. Ho
do I delete these spaces in one mass change? I've got 20,000 rows o
data, I can't do it one by one
 
B

Bob Umlas Excel MVP

Change the VLOOKUP from
=VLOOKUP(cellref,...)
to
=VLOOKUP(TRIM(cellref),...)
 
G

Gord Dibben

aj

Manually......

In an adjacent column enter =TRIM(cellref). Double-click on the fill-handle
at bottom right of this cell and it will replicate down as far as data in the
original column.

When happy with the results, Copy the column with the TRIM formula and paste
special>values(in place).

Then delete original column.

VBA Macro..........

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP
 
A

ajpowers

Thank you everyone that posted! All the advice is such a big help!

Best regards,

Am
 
Top