Converting Rows into Columns

[Bobbins]

Afternoon All,

I have been able to read in via OCR from the excellent PDF Reader tools some data which i have been able to convert to rows, but now I am a bit stuck. My data looks like this:

ACME Company
Stock Count
Count Volume Cost
Soft Toys			
Big Blue Box 3 28 10
Red Box	10 58 94
Books			
Big Blue Box 9 76 6
Red Box	8 38 12
Undefined			
Yellow Box 12 79 53
End of stock count

But what i actually want is 

	Count	Volume	Cost
Soft Toys
Big Blue Box	3	28	10
Red Box	10	58	94
Books
Big Blue Box	9	76	6
Red Box	8	38	12
Undefined
Yellow Box	12	79	53

Handily the words stay the same, but struggling with separating. What's the best way to go about this? I did imagine having many loops for each line which seems a bit excessive, Any thoughts?

Thank you 

[vsoni]

regex will help here, as you can parse out the numbers that are followed by a space.

the attached should help you get started.

[Luke_C]

Hi @Bobbins 

Here's an example of how you could approach this using regex to help parse. Others might have a more elegant answer but this should get you in the right direction

[atcodedog05]

Hi @Bobbins 

Here is my take similar to above but slightly different.

Workflow:

Hope this helps : )

[Bobbins]

Thank you all, I am just trying to fiddle this into my real life data (I forgot in the example to include a , in a number!) but this is giving me a good reason to fiddle with the Regex feature

[atcodedog05 · Accepted solution]

Hi @Bobbins 

Here is a helpful resource on Regex.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

Hope this helps : )

[Bobbins]

Thank you all, they all work but I have selected the last one as a solution because I am finding that Regex is not something you can just copy and paste and hence trying to make you learn it is better then not!