Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Newbie
    Joined
    Jun 2022
    Posts
    2
    Location

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

    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.

  2. #2
    VBAX Newbie
    Joined
    Jun 2022
    Posts
    2
    Location
    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •