I have 2 columns in a file in which some of the data ends with 10 which indicates census year 2010 and I need to change fields that end in 10 to 20. There are 2 other conditions where the data ends in 10M and 10X and I need those changed to 20M and 20X respectively. Also, the 2nd column is a formula, typically one field divided by another.
Example
Field1 Field2
HH10 HH10/HH10
HH10M HH10M/HH10M
HH10X HH10X/HH10X
HH110A10 HH110A10/HH10
HH10C10M HH10C10M/HH10M
HH101010X HH101010X/HH10X
Desired Result
Field1 Field2
HH20 HH20/HH20
HH20M HH20M/HH20M
HH20X HH20X/HH20X
HH110A20 HH110A20/HH20
HH10C20M HH10C20M/HH20M
HH101020X HH101020X/HH20X
I know this can be done with multiple passes starting with a base formula of
REGEX_Replace([Fiedl1], "(.+)(10)", "($1)(20)") and adding more that include the M and X and then another for Field2 that contains the formula
But, is there a way with REGEX_Replace to utilize OR conditions or other options to modify each column with one expression to include the M and X scenarios?
