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

[mkseto]

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.