Concatenate formula for variable columns

Good afternoon all,

Still new to VBA and I was looking for some assistance. I have a large file I pull from SAP and bring in as a text file (150,000+ lines). The file can only be brought in using tab delimited.

My issue is there are two columns however that have their contents split up (column headers 'reference' and 'text'). Reference, the first of the two always begins at array 16 when pulling in the file delimited and the column headers are always located in row 9. Sometimes the contents of reference split up into 5 separate columns, 3 separate columns, etc. Text, the second of the two columns always follows the last column of what Reference has been split up into.

What I need to do is find the unknown column that text is located in the header row, insert a column and concatenate all cells to the left of the new column until I hit the static column Reference begins (16 or column P). Trouble is I don't know how many extra columns separate the reference column from the text column and it changes from week to week usually.

Please Login or Register  to view this content.

FormulaR1C1 does not do the trick because the number of columns change from week to week

Thanks!

Here is a very condensed down version of the file and as the format appears when I finish the delimited pull in from the .txt file I export from SAP. I tried to show how the text in the 'reference' column gets broken up and there is no exact science on how many columns down the remaining text gets sent. The number of columns will also change week to week usually - so my issue is I either don't reference enough columns in my concatenate or & formula -- or too many carrying it over into the 'text' column and screwing up that data.

Never mind I think I figured it out

Please Login or Register  to view this content.