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

Regex

Accepted answers

flying008

Hi, @Pranab_C

Because you first line string have no leading space, but your sample data has, so you only need modify the parse expression from ^["\s]+([[:alpha:]]+)\s+(\d+)\s[\d\s]+(?:([[:alpha:]\s]+)\s+(\d+))? to ^["\s]?([[:alpha:]]+)\s+(\d+)\s[\d\s]+(?:([[:alpha:]\s]+)\s+(\d+))? , then all done.

Tips: only change the first + to ? .

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

录制_2023_12_22_15_30_20_854.gif

******

If can help you get your want, please mark it as a solution and give a like for more share.

All comments

CoG

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

Arizona

Colorado

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.

录制_2023_12_22_11_45_41_15.gif

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\-]+?\)(?="|$)', '')&#8203;

Parse:

^["\s]+([[:alpha:]]+)\s+(\d+)\s[\d\s]+(?:([[:alpha:]\s]+)\s+(\d+))?&#8203;

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.

录制_2023_12_22_14_00_08_530.gif

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

Test_Regex.yxzp

flying008

Hi, @Pranab_C

Tips: only change the first + to ? .

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

录制_2023_12_22_15_30_20_854.gif

******

If can help you get your want, please mark it as a solution and give a like for more share.

Pranab_C

It should be in each row, I understand the data is not standard but here is how the data is in most cases. Please see attached the PDF file and the way data would be in 99.9% cases. Your help in getting this extracted would be greatly appreciated.

Page_from_PDF (1).pdf

flying008

Hi, @Pranab_C

Maybe you can find the macro of readpdf or readword from gallery.

录制_2023_12_23_09_30_39_999.gif

Table_No	RowID	Location	Workdays (W)	COVID-19 Workdays (CW)	Non-Workdays (O)	COVID-19 Non-Workdays (CO)	Not Specified	Total
1	1	Oman (OM)	0	0	1	0	0	1
1	2	Qatar (QA)	0	0	2	0	0	2
1	3	United Arab Emirates (AE)	147	0	66.5	0	0	213.5
1	4	United States Arizona (US-AZ)	0	0	8	0	0	8
1	5	United States Colorado (US-CO)	0	0	8	0	0	8
1	6	United States Missouri (US-MO)	70	0	47	0	0	117
1	7	United States Utah (USUT)	0	0	15.5	0	0	15.5
1	8	Total	217	0	148	0	0	365

Pranab_C

Thank you but the issue is that this workflow would be run in gallery and directory would not work in that environment. We are currently using R code to extract PDF, its working fine for the entire PDF except this page. Any suggestions or help would be much appreciated.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335