Deleting an inadvertant space

[DougRobertson]

Hello,

I deal with making reports from radio stations.
When sending me data in Excel, people sometimes inadvertantly leave a space at the end of a stations name. When trying to match up names in n macro, this is a case of "what you don't know (or can't see) can definitely hurt you!"

For instance, if the macro is looking for station "CKNW", and the incoming data only has "CKNW ", we know there's going to be a problem.

Is there VBA code that can delete spaces and just leave hard characters when gleaning information?

Thanks,

~ Doug

 

[R Pelle]

Have a look at Trim(), that should sort it out for you.

 

[Marcelo Branco]

Hi Doug,

Someone posted, in this forum, the code below some time ago. I used it a couple of times with success.

Try it in a copy of your workbook

Sub TrimALL()
   'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
   '-- [URL]http://www.mvps.org/dmcritchie/excel/join.htm#trimall[/URL]
   ' - Optionally reenable improperly terminated Change Event macros
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro
 
   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If
 
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
 
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
 
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
 
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

HTH

M.

 

[mikerickson]

To do a bulk column, you could use TextToColumns with a space delimiter.

 

[DougRobertson]

Thanks for your responses everyone.

It looks as if maybe this is new with Excel 2010, but they have a Trim() function in VBA as well as Excel, that is exactly what I'm looking for. Doesn't look like this was always the case though.

Works like a charm!

I appreciate the help, and hope I can reciprocate some day,

~ Doug