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
Solved! Go to Solution.
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?
@alexnajm - What delimiters would I use to peel out the substrings when they're not all consistent?
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.
I tried this RegEx "(^[a-zA-Z ]+$)" but it's not working.
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...
@alexnajm - Want to capture a substring that starts with a letter and ends with a letter ignored everything else.
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?
@knnwndlm one way of doing this
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 |