Clean Up Messy Data in Different Rows and Columns

Question

Hi - I currently have a very messy data set as shown below:

Column1Column2Column3Column4Column51010101$500$200$300 $100$600   1010102$600$100  $200$300$800   1010103$500$800 $900$100   $200

I'd like to convert the data above using the 7-digit 101010x identifier but the data is all over. Ideally, I want to begin each row with the 7-digit "ID" and read in each $ amount, excluding nulls until it hits the next 7-digit "ID". From there, the next row should begin. Below is the output I'm looking for:

IDCost1Cost2Cost3Cost4Cost51010101$500$200$300$100$6001010102$600$100$200$300$8001010103$500$800$900$100$200

Is this possible in Alteryx? This is the first question. Sometimes in the raw dataset, there may be random text in some cells, not sure if they can be ignored. I believe the raw data set is actually too messy to be 100% solved by Alteryx. Thanks!

grossal · Accepted Answer

Hi @idk_code,

good news for you, I think it's definitely possible with Alteryx and here is how to do it!

Output:

You can easily add another Filter to extract your additional messy data or send a sample dataset with messy rows and I'll adapt the workflow. Workflow attached.

Best

Alex