How to parse regex with my data?

[maryyan]

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)

[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)

[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!

[MichalM · Accepted solution]

@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 

[maryyan]

Wow. I cannot believe so many people have already responded!

THANK YOU SO MUCH!! (I am in tears!)

All the answers are great! Among all, 

@MichalM Your solution is easiest for a green-hand like me to understand. Thank you!

Thanks a lot EVERYONE!!!