Parsing and repeating the first data element in each row

[alphabex18]

Hi all, 

I am trying to parse the data from an EDI transmission file. The below is my current output after a transpose tool. I want to separate the text between the asterisks, however the very first string of text in each line (the text before the first asterisk) I need to repeat for each additional data element in that same line. 

I want my outcome from the table above to look like this. I am showing only the first three lines from the above table.

Ref	Value
ISA	00
ISA
ISA	00
ISA
ISA	ZZ
ISA	330115986
ISA	ZZ
ISA	473103726
ISA	180815
ISA	1006
ISA	U
ISA	00501
ISA	000001171
ISA	0
ISA	T
ISA	>
GS	BE
GS	CC
GS	OH
GS	20180815
GS	1006
GS	1171
GS	X
GS	005010X2201A
ST	834
ST	11710001
ST	005010X2201A

Thank you for any advice!

[ponraj]

Possible to shared the sample data in excel 

[JohnJPS · Accepted solution]

Hi @alphabex18

Perfect scenario for Alteryx: try two Parse tools in a row: the first, split your "Value" field to columns, only two columns and "Leave extra in the last field".  The second, split the new "Value2" field to rows.  Boom: you're done.  :-)

Hope that helps!

John

[alphabex18]

John, I love you!!!! Thank you so much. 

[ajay1998]

Great resolution