Parsing Certain Patterns in a String

[knnwndlm]

Hi SME,

In the file below, I have a string in column C that contains both description and rate that I would like to peel out so that I would get the output shown in columns F-H.  I have no idea where to even begin with a RegEx expression to parse out the two columns G and H.  Please help.

Thanks,

kwl

[alexnajm]

Text to Columns tool should do the trick! But you don't have consistency with the delimiters in terms of having them separate out expectedly - is this expected?

Like you said, Regex can help... but only if there's an expected pattern to parse. What would that pattern be?

[knnwndlm]

@alexnajm - What delimiters would I use to peel out the substrings when they're not all consistent?

[alexnajm]

Yep that's my point... what is the pattern you can use if the delimiters are inconsistent? If you can tell us that, we can figure out a solution.

[knnwndlm]

I tried this RegEx "(^[a-zA-Z ]+$)" but it's not working.

[alexnajm]

In layman's terms, can you tell me how you'd want to parse through this? i.e. what is the pattern that we'd be able to use to identify what to parse out?

This pattern above just seems to give us one or more letter character...

[knnwndlm]

@alexnajm - Want to capture a substring that starts with a letter and ends with a letter ignored everything else.

[alexnajm]

Based on the data provided though, there are also non letter characters to consider (spaces, percentage signs, etc.) so that's why your original one was struggling to work.

also, it looks like you are trying to NOT parse anything with "General Tax". Can you please revise?

[binuacs]

@knnwndlm one way of doing this

[flying008 · Accepted solution]

Hi, @knnwndlm 

FYI.

Input		Output
Value2		EXPECTED DESCRIPTION	EXPECTED RATE
15001104 - UNITARY 1% TAX RATE|805-654-3181|1.000000|$16,578,926.48		UNITARY 1% TAX RATE	1.000000
15001102 - UNITARY-CO AVG D/S RTE|805-654-3181|0.621247|$10,299,608.34		UNITARY-CO AVG D/S RTE	0.621247
||1.621247 General Tax Subtotal:|$26,878,534.82			1.621247
15005101|- UNITARY ELECTRIC 1%||1.000000||$389.37		UNITARY ELECTRIC 1%	1.000000
15005102|UNITARY EL-CO D/S RTE|805-654-3181|0.621247||$241.89		UNITARY EL-CO D/S RTE	0.621247
|||1.621247 General Tax Subtotal:||$631.26			1.621247
14001101 PROP 13 MAXIMUM 1% TAX|805-654-3181|1.000000||$603.00		PROP 13 MAXIMUM 1% TAX	1.000000
11029103 - EL SCH BD OCEAN VIEW|805-383-1982|0.035000||$21.10		EL SCH BD OCEAN VIEW	0.035000
11029104 - EL SCH BD OCEAN VIEW 2|805-383-1982|0.033600||$20.26		EL SCH BD OCEAN VIEW 2	0.033600
11029105 - EL SCH BD OCEAN VIEW 3|805-383-1982|0.007800||$4.70		EL SCH BD OCEAN VIEW 3	0.007800
12007102 - HI SCH BOND OXNARD|805-383-1982|0.004100||$2.48		HI SCH BOND OXNARD	0.004100
12007103 - HI SCH BOND OXNARD #2|805-383-1982|0.011800||$7.11		HI SCH BOND OXNARD #2	0.011800
12007104 - Hl SCH BOND OXNARD #3|805-383-1982|0.017100||$10.31		Hl SCH BOND OXNARD #3	0.017100
12015103 - VTA COMM COLLEGE BD|805-383-1982|0.014400||$8.69		VTA COMM COLLEGE BD	0.014400
18750101 - METROPOLITAN WIR|866-807-6864|0.003500||$2.14		METROPOLITAN WIR	0.003500
18031102 - CITY OXNARD DIST #1 BOND|805-721-2924|0.045750||$27.58		CITY OXNARD DIST #1 BOND	0.045750
||1.173050 General Tax Subtotal:||$707.34			1.173050