erasing space/blank in a cell

[thread]

Hi
i'm doing alot of vlookup work and very common to facea problem in
linking becaouse of additional space or blank line
does anyone have an idea how can it be overcomed?

 

[Eduardo]

Hi,
try this, highlight the column where you have the information, ctrl H, that
will take it to the replace screen, let's say you have 1 blank space, in find
what press the space bar one , then tab to replace with, don't do anything
here and press replace all. hope this help

 

[torben.svensson]

Hi
i'm doing alot of vlookup work and very common to facea problem in
linking becaouse of additional space or blank line
does anyone have an idea how can it be overcomed?

Hi,

there is a function called trim. This enables you to take out all the
spaces at the beginning of the cell. Maybe that one helps

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP