Parsing .txt file to columns when there are subsections with headers

[J-Riedel]

Hi,

I am struggling on an idea on how to parse some data to columns.

The report comes from an external party as a .txt file and I cannot see any sort of delimiters.

The report is in the structure of:

Main Header

Subsection 1 Header

Column header 1   Column header 2  Column header 3 Column header 4

Data column 1        Data column 2      Data column 3      Data column 4

Data column 1        Data column 2                                   Data column 4

Subsection 2 Header

Column header 1   Column header 2  Column header 3 Column header 4

Data column 1        Data column 2      Data column 3      Data column 4

I was able to isolate the column header and data columns and use a Regex formula to replace the empty spaces with "|" in order to do a text to columns.  Regex_Replace([Field_1],"\s{2,}",'|')

This worked fine, but in the Subsection 1, in the 2nd data row, column 3 is blank, therefore, the date in column 4 moves to column 3.

Any suggestions on how I can make this work?  I have attached a sample text file.

[binuacs · Accepted solution]

@J-Riedel one way of doing this

[J-Riedel · Accepted solution]

@binuacs Thank you for this solution! This worked perfectly for what I needed.  I appreciate the quick response and solution.