Alter Data formula help

[Brad1]

How do I convert:

A03   to   1103

B09   to   1209

(A means 11, B means 12)

Thanks in advance.

[Kenda · Accepted solution]

Hey @Brad1! I would suggest using the following expression in a Formula tool:

REGEX_Replace(REGEX_Replace([Field1], "A", "11"),"B","12")

Hope this helps!

[Brad1]

My formula is not like yours.  I think I like yours better.  Mine is running now.  Waiting to see what comes out.  I don't think mine will address if more than two digits after the "A".  Looks like yours will.

[Brad1]

Or maybe it won't.

[Brad1]

I should have mentioned the last two digits could be any two digits.  Here's what I wrote and it works.

IF(Left([CHAIN], 1) = 'A') THEN '11' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'B') THEN '12' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'C') THEN '13' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'D') THEN '14' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'E') THEN '15' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'F') THEN '16' AND RIGHT([CHAIN],2) ELSEIF (Left([CHAIN], 1) = 'G') THEN '17' ELSE [CHAIN] ENDIF

[Brad1]

Yeah, yours is better.  Thanks!!!

[Kenda]

@Brad1 Because you are checking more than just A and B, you could consider using a Find and Replace tool instead. This may be less messy and could be easier to update if you need to in the future. In the F anchor, you will feed in your initial field (I named mine find). On the R side, you will feed in a Text Box that you create that has one column with the string you want to find (here, A, B, C, etc.) and one column with the replacement value (11, 12, etc.) as a string. This will output just the find field with the replacements that took place. Either this way or the formula before should work!

[patrick_digan]

@Brad1 You may be able to use the find replace tool as well. I've attached the start of what that would look like.

Just a thought!

EDIT: I see @Kenda and I are on to the same idea.