Convert text file to excel with categorized columns

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 identifier Screenshot 2025-02-05 104947.png s.

Help

Accepted answers

Luke_C

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

New Workflow3.yxmd

All comments

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!

Alteryx Community Example.xlsx

Luke_C

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

New Workflow3.yxmd

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335