Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns or TRIM?
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 -- Kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns or TRIM?
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 InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On Mon, 5 Feb 2007 12:07:12 -0800, Kevin wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns or TRIM?
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... "Kevin" wrote: 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 -- Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating text columns layout with page breaks in Excel 97 | New Users to Excel | |||
Copy from a Cell to a text box. | Excel Worksheet Functions | |||
Search & delete ANY text in select columns | Excel Discussion (Misc queries) | |||
How do I convert from text to columns automatically on import? | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions |