Regex to extract Text and Number into different columns

[Pranab_C]

Hi Champions, 

i need help in extracting text and numbers into different columns from this

What I have is this:-

" Arizona 0 0 (US-AZ) Colorado 0 0 (US-CO)"

"Qatar (QA) 0 0 United Arab Emirates 147 0 (AE)"

" Missouri 70 0 (US-MO)"

" Utah (US- 0 0 UT) Total 217 0"

What I need is, i.e. Arizona in one column and zero in another

Arizona-0, Colorado-0

Qatar-0, United Arab Emirates 147

Missouri-70

Utah-0, Total 217

[CoG]

What does the comma mean? Should the output look like this:

Arizona

0

Colorado

0

Or should each pairing be in a new row?

Arizona	0
Colorado	0

Also, there is very little standardization in your data (Very important for Regex). Is what you have included, representative of every case in your data?

[flying008]

Hi, @Pranab_C 

FYI.

Input
Txt
Arizona 0 0 (US-AZ) Colorado 0 0 (US-CO)
Qatar (QA) 0 0 United Arab Emirates 147 0 (AE)
Missouri 70 0 (US-MO)
Utah (US- 0 0 UT) Total 217 0
Output
Txt	Match	State	ID	SKU	Num
Arizona 0 0 (US-AZ) Colorado 0 0 (US-CO)	Arizona 0 0 Colorado 0 0	Arizona	0	Colorado	0
Qatar (QA) 0 0 United Arab Emirates 147 0 (AE)	Qatar 0 0 United Arab Emirates 147 0	Qatar	0	United Arab Emirates	147
Missouri 70 0 (US-MO)	Missouri 70 0	Missouri	70
Utah (US- 0 0 UT) Total 217 0	Utah  0 0  Total 217 0	Utah	0	Total	217

Spoiler

Formula:

REGEX_Replace(REGEX_Replace(REGEX_Replace([Txt],  '\s\([a-z\-]+?\)(?=[^"$])', ''), '\([a-z\-]+?([\d\s]+?)[a-z]+?\)(?=[^"$])', '$1'), '\s\([a-z\-]+?\)(?="|$)', '')​

Parse:

^["\s]+([[:alpha:]]+)\s+(\d+)\s[\d\s]+(?:([[:alpha:]\s]+)\s+(\d+))?​

[Pranab_C]

Thank you so much for taking a look at this, worked for all except the row that contains

"Qatar 0 0 United Arab Emirates 147 0"

[flying008]

Hi, @Pranab_C 

So, like this string of "Qatar 0 0 United Arab Emirates 147 0", what result are you want get ?

[Pranab_C]

Input

Qatar 0 0 United Arab Emirates 147 0

Desired Output

Qatar| 0 | United Arab Emirates| 147|

[flying008]

Hi , @Pranab_C 

About the join character for each, you can type a formula after split to adjust it. 

[Pranab_C]

Thank you once again for quickly taking a look at this, can you share the workflow that you have created.

[flying008]

Hi, @Pranab_C 

There is only 2 step for workflow, and i post all expression in above post, you just copy it to your canvas to get it. 

If you can post your want output result data as table, maybe i rebuild the flow as pre.

[Pranab_C]

Hi Thank you once again, here is the test workflow package, you would notice that its not working for the first row only. It works perfectly for the rest of the information