How to parse regex with my data?

Hi Everyone,

I am fairly new to Regex but I need to parse out information from "StatesOps" field as following sample shows:

1. State: for example for 100440, State parse out of "StatesOps" will be GA, FL, SC, AL and TN

2. # offices: for example for 100440, # of Offices parse out of "StatesOps" will be 116 for GA, 99 for FL, 38 for SC and 37 for AL, and 11 for TN.

Attached please find my trial of Regex tool which gives me nothing as result.

Any of your suggestions and help will be really appreciated!!

ID	STATE	StatesOps
1018216	GA	GA(16)
100440	GA	GA(116),FL(99),SC(38),AL(37),TN(11)
100144	NY	FL(8),PA(8),CA(7),NY(4),TX(4),OH(3),DC(2),DE(2),GA(2),IL(2),NJ(2),RI(2),AL(1),AZ(1),CO(1),CT(1),IN(1),KY(1),LA(1),MA(1),MI(1),MO(1),NC(1),VA(1),WA(1)
100233	AL	FL(309),TN(218),AL(211),MS(126),GA(117),LA(98),TX(89),AR(81),MO(64),IN(52),IL(48),SC(22),KY(11),IA(8),NC(7)
1006672	IL	FL(18),CA(8),IL(7),TX(6),AZ(3),MI(2),CO(1),CT(1),DC(1),GA(1),MA(1),MN(1),MO(1),NV(1),NY(1),OH(1),WA(1),WI(1)

regex.PNG

Regex

Parse

Accepted answers

MichalM

@maryyan

An alternative to Regex is to use the Text-to-columns tool

First use it to split the data into rows on each comma - these will create a row per state and number of offices pairing
Followed by splitting into columns on parentheses

stateops.yxmd

All comments

MichalM

@maryyan

What would you like the output to be?

PeterA1

@maryyan Might take some playing with the output (renaming columns and such) but essentially the Regex Tokenize function will work for this. What you can do is: either split to rows, since then the ID will be tied to each record that is tokenized and you can work with this later. Or just parse to columns (you will need to guess how many to add and can remove extras with select tool)

Regex Tokenize.yxmd

ChrisTX

Try this:

Regular expression: (\w+)$\d+$

Output method: Replace

Replacement text: $1

bpatel

Hi @maryyan,

You could also possibly use the text to columns tool depending on what your final output needs to be. Here is what i came up with.

hope this helps!

parsing data.yxmd

MichalM

@maryyan

An alternative to Regex is to use the Text-to-columns tool

First use it to split the data into rows on each comma - these will create a row per state and number of offices pairing
Followed by splitting into columns on parentheses

stateops.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703