Trim text with VBA

[Eshton]

I have a number of data imported from an other application into Excel 2010. Unfortunately some of the text cells have a space at the front so I can not use in VLOOKUP. Is there a way to "Trim" this space with VBA code for all the contents of say column "H".

I am as you gather a novice in VB so would appreciate your advise.

Many Thanks

 

[VoG]

Try selecting the data then running the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

 

[Eshton]

Try selecting the data then running the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

Brilliant. Thanks for this. Macro worked a treat.

 

[Eshton]

Brilliant. Thanks for this. Macro worked a treat.

Just noticed that the code removes all spaces, including those between words. Can I possibly amend this to skip the spaces between words?

code is:

Range("G:G").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.SpecialCells(xlConstants).Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Selection.SpecialCells(xlConstants).Replace What:=Chr(32), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


Thanks again.

 

[VoG]

Try

Range("G:G").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.SpecialCells(xlConstants).Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
For Each cell In Selection.SpecialCells(xlConstants)
    cell.Value = Trim(cell.Value)
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[Eshton]

Try

Range("G:G").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.SpecialCells(xlConstants).Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
For Each cell In Selection.SpecialCells(xlConstants)
    cell.Value = Trim(cell.Value)
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Perfect. Thanks a lot.