nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Parsing Issue.xlsx

Regex

Accepted answers

flying008

Hi, @knnwndlm

FYI.

录制_2024_08_06_17_11_15_967.gif

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

All comments

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?

binu_acs

@knnwndlm one way of doing this

Rate_06082024.yxmd

flying008

Hi, @knnwndlm

FYI.

录制_2024_08_06_17_11_15_967.gif

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

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15908

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703