Replacing numerical values

Question

I have two lists that I am joining to form one large dataset. In list one, I would like to scan a column and replace all of the number 1s with the number 5, the 2s with 8, and the 3s with 7. For the second list, I would like to replace all the 1s with 6 and the 2s with 9.  I have tried using the switch and replace functions within the multi-field formula tool with no success. I'm sure this is a simple request for most, but I can't seem to figure it out. Any assistance would be greatly appreciated!

tom_montpool · Accepted Answer

My first suggestion would be to ensure that the column you are working on is actually storing the number as a numeric data type...this has implications for how you would do the replace. For example, if the column is a string, then you need to replace "1" with "5" whereas if it is integer you would replace 1 with 5.

I would probably use an if statement in the Formula tool to do what you want...it would look something like this (assuming your field is a numeric data type)

IF [COL]==1 THEN 5

ELSEIF [COL]==2 THEN 8

ELSEIF [COL]==3 THEN 7

ELSE [COL]

ENDIF

You could also use the "Find Replace" tool (in the Join toolbox), which works just as the find/replace in Excel.

There are other ways, but hopefully one of these does the trick!

Have fun!