Combine TXT, copy to Excel, remove blank rows and rows not starting with numbers, extract number from txt strings, txt-2-column

mkseto

New Member
Joined
Aug 14, 2018
Messages
13
I perform the following on a regular basis, would like to automate it as much as possible with VBA, hope someone can help with this complicated task:
  1. Combine a large number of TXT files in a folder (could be 1 or more pages, some has no data other than page header type of text)
  2. Copy all data from the above combined TXT file onto an Excel worksheet
  3. Remove all blank row, page headers, and any other rows without a “REF#”. In other words, leaving only rows with a numeric value in the beginning of the row, along with ONE header row (there are actually 2 header rows per page, it would be ideal to keep both but I’m keeping only one in my current manual process since it’s easier), as well as rows starting with the text "STORE NO".
  4. Insert a new column and fill it with the numeric values extracted from the store "STORE NO" field.
  5. Use the Text-to-column function to separate the various fields. Add "STORE NO" as header for column "A".
For #1 and #2, I have been using DOS prompt to combine the files and copy to Excel. It would be ideal to have a macro to combine the files and copy onto the Excel worksheet (and ideally for the macro to prompt user to select the folder where all the TXT files are stored).

To accomplish #3, I first have to insert a left-most column (i.e. new Column "A") to number the rows, then sort the sheet by column "B" (previous column "A" before the inserted column), then remove all the blank rows and other rows I don't need except the following:
  • one header row (out of many)
  • rows that has a numeric value at the beginning of the row
  • rows that has the text “STORE NO” at the beginning
Finally, I re-sort the file to its original order via column "A" and in turn delete column "A".

For #4, I insert a column to the right (i.e. new column “A”), then for each cell in column “A”, apply a formula to use the wildcard search for the text "STORE NO" and, if found, copy the whole row. After replacing the formula in column "A" with its text strings, I then use TEXT TO COLUMN function to remove everything in each cell (of column "A") except the numeric value (i.e. the number after the text “STORE NO”). Finally, I add “STORE NO” as the column header for column “B”, then delete column “A”.

For #5, obviously, I use Text-to-column to separate the fields and perform some minor/final formatting to each column.
 

Attachments

  • Sample TXT 1.PNG
    Sample TXT 1.PNG
    42.4 KB · Views: 12
  • Sample TXT 2.PNG
    Sample TXT 2.PNG
    52 KB · Views: 11
  • Before.PNG
    Before.PNG
    66.1 KB · Views: 10
  • After.PNG
    After.PNG
    98.1 KB · Views: 11

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,147,476
Messages
5,741,349
Members
423,656
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top