VBA to make 1st column of a text to columns operation "CLEAN"

[amaskary]

Hi Friends. I download a system-generated html-based report, which I naturally import to Excel. However, when I apply the Text-to-Columns operation, there is usually a bunch of rows which start with a weird character which ruins the consistency of the column, which sometimes causes errors in later operations. Therefore, my question is: I do not want to use the "Fixed width" option in text-to-columns, however, how do I ensure that the first column only is at least of a fixed width/ containing a consistent number of characters throughout the worksheet? Hope my question is clear. Thanks.

[amaskary]

This is an example of the character I'm talking about. (it looks like a space). it's the line starting 2231014. The point is, I just want to make it consistent in width with the preceding rows so that the Text-to-Columns operation comes clean.


2221008 Employee's Retention -322,630.14 0.00 0.00 -322,630.14
2231001 Accruals - Staff Payments -0.02 0.00 0.00 -0.02
2231002 Accruals - Marketing&Shipping -128,774.66 128,774.66 128,774.66 -128,774.66
2231004 Accruals - Feed Gas (Inv Accrua -13,419,721.00 13,419,721.00 45,604,175.00 -45,604,175.00
2231005 Accruals - Plants Maintenance ( -142,100.87 0.00 0.00 -142,100.87
2231007 Accruals - General Admn. -106,950.61 0.00 0.00 -106,950.61
 2231014 Unearned Rental Income -491,870.63 81,978.44 0.00 -409,892.19
3011001 Share Capital - GOSO -33,724,800.00 0.00 0.00 -33,724,800.00

[arnelgp]

create a UDF that will "clean" your html file from non-printable (or special) characters before importing it.