Excel/Macro to Scrub Data
I have a database that was created by scanning a document with OCR software. Column (D) has $ values, but they formatted very strangely, and randomly, as illustrated below:
$1 .43 BLANK BEFORE DECIMAL
$6. 77 BLANK AFTER DECIMAL
$1 . 96 BLANK BEFORE AND AFTER DECIMAL
$1 . 05 BLANK BEFORE AND AFTER DECIMAL
$3. 62 BLANK AFTER DECIMAL
$38. 25 BLANK AFTER DECIMAL
$1 . 37 BLANK BEFORE AND AFTER DECIMAL
$2. 53 BLANK AFTER DECIMAL
Is it possible to write a macro to evaluate every cell, find and delete the blank characters, and leave the remaining characters as a numeric value?
I would just select the column (assuming all the data is in a single column) and do Replace,
put in a space in the first textbox and nothing in the second textbox and then choose replace all
Using your data that produced:
for me. (I did some formatting after all the spaces were removed).
So I would try that first. If that doesn't work, send me the data in a worksheet and I will see if the spaces are actually spaces or some other non-printing character.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here