Text to Columns or TRIM?

[Guest]

Hi

I have a sheet with 20 columns, some text, some numbers or dates. The
columns with text have lots of empty text after the last character. I can use
the TRIM() function to get rid of it but there are almost a million cells so
it time consuming and excel is prone to crash.

Is there a simpler way to get rid of the data. Note the file is originally a
csv and I am ultimately trying to upload it into an access database so
potentially there are some better ways perhaps just to upload directly to
Access?

thanks

 

[Gord Dibben]

Kevin

Note: with a Million cells this will take a while.

I tested with 10 full columns and it took 8 seconds.

Sub Trim_Spaces()
'Modified by Gord Dibben Feb. 5th, 2007 from original by
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Trim(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[Guest]

I assume you mean just a bunch of spaces after the last word in the cell.
Another option, depending on if this occurs elsewhere in your sheet, is to go
for a Find/Replace. In the find, hit, say, 2 spaces. then in the replace
leave as is, then replace all... Of course, if you have other spots where a
doulbe space is needed, you may want to go with 3 or whatever you can get
away with...