Use of OR logic in Regex Replace

[bwiedo]

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?  

[ChrisTX]

Try this regex:

.*(10)([M|X]{0,1})

Chris

[bwiedo]

I see where this recognizes the patterns I'm looking for.  How would the replace portion of the formula look?

[binuacs · Accepted solution]

@bwiedo One way of doing this