I need to parse complex, variable length strings (from a csv export from a purchasing system when coding is split) where each string may contact one record or multiple records. This example contains three records. The fields may contain 2 to 99 individual records.
Some of the data has been replaced with fake data to preserve business confidentiality:
Records start with number of accounts ("3 Accounts") not repeated in the string, then a numeric amount. Then each significant field (Proj ID, BU, Account, Team, Sub-Team, Project Code) in each record is repeated three to six times - once in parenthesis, once w/ Field Name: 00000 format, and one to four times in "open strings". The end of each record is a semicolon.
I need to extract each significant field ONCE and remove all redundant or unnecessary data. And, of course, multiple records need to be separated into multiple lines. To make it more complicated, these strings have cells (fields) both left and right of them, mostly in good shape from the export. So I have to count the number of actual records in the string and store that to use for loops that copy the data to the left and right down the appropriate number of times.
Non-split records in this field look like this:
And are easily parsed using hyphens as a delimiter. So the result of parsing the "split" fields should be 6 fields separated by hyphens, even if the fields are empty, by however many lines are necessary for the number of records.
The solution could be VBA, formulas, or a combination of both. I've been racking my brain but can't really wrap my head around a solution. Thank you in advance.
Some of the data has been replaced with fake data to preserve business confidentiality:
Records start with number of accounts ("3 Accounts") not repeated in the string, then a numeric amount. Then each significant field (Proj ID, BU, Account, Team, Sub-Team, Project Code) in each record is repeated three to six times - once in parenthesis, once w/ Field Name: 00000 format, and one to four times in "open strings". The end of each record is a semicolon.
I need to extract each significant field ONCE and remove all redundant or unnecessary data. And, of course, multiple records need to be separated into multiple lines. To make it more complicated, these strings have cells (fields) both left and right of them, mostly in good shape from the export. So I have to count the number of actual records in the string and store that to use for loops that copy the data to the left and right down the appropriate number of times.
Non-split records in this field look like this:
And are easily parsed using hyphens as a delimiter. So the result of parsing the "split" fields should be 6 fields separated by hyphens, even if the fields are empty, by however many lines are necessary for the number of records.
The solution could be VBA, formulas, or a combination of both. I've been racking my brain but can't really wrap my head around a solution. Thank you in advance.
