Convert text file to excel with categorized columns

[danielrtjr]

I am trying to automate the process of exporting a text file to an excel file, and then breaking out the data into the proper format, with column headers. Please see the screenshot below. The screenshot shows a raw text export from a source, and I am trying to create a new column that shows the identifier highlighted in red on each line. For example, the column would be called "identifier" and the identifier for lines 2-5 would be "00000576A", but I want to have a new identifier for lines 8-17, and so on. Is there an easy way to do this? There are thousands of lines with many individual identifiers. 

[Luke_C]

Hi @danielrtjr 

Are you able to share sample files with this data? To do this you'd want to use the multi-row formula tool to parse out the identifier and fill it down. So if the formula detects the identifier in the current row it'll be parsed out, if it doesn't it'll take the value that was parsed  from the previous row

[danielrtjr]

Luke,

Thanks for the response. Please see the attached file. I understand your theory of how to get there but I think I may need an example created in designer to help me totally understand.

Thanks!

[Luke_C · Accepted solution]

Hi @danielrtjr 

Give this a shot, needed to make some assumptions on the larger dataset with the regex but hopefully gets you in the right direction