Need macro to eliminate blank space

[Schwoerer]

Hello,

I have a problem when I import a csv file into Excel and Access, there are several hundred lines that are imported with 3 blank spaces before the text names in the field. This causes a problem when sorting and running sum totals, as it treats these names as a different spelling. How can I create a macro to take the three spaces out and shift the text to the left 3 spaces?

Thank you in advance!

 

[PA HS Teacher]

=Right(A1,Len(A1)-3)

 

[Smitty]

You can select the column with the offending spaces and goto Data-->Text to Columns-->Fixed-->Set the delimiter-->Destination should be the original starting point and finish.

Hope that helps,

Smitty

EDIT: Welcome to the Board! Go Frogs!

 

[KenWright]

Pop this in your personal.xls and then select your entire dataset and run it

Sub a_TrimALL()
   'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
   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