Delete Spaces

[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

 

[Bob Umlas Excel MVP]

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

 

[Andy B]

Hi

Have a look here for a macro to do the job:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
The alternative is to use a helper column alongside your existing data. Post
back if you want to go that way.

 

[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

 

[ajpowers]

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

Best regards,

Am