Hi, I have a column that has a drop down box that has different options: Very Low, Low, Medium, High, Very High.
I have set up a substitute formula in a new column that replaces the above phrases with a number: Very Low = 1, Low = 2, Medium = 3, High = 4, Very High = 5
My substitute formula looks like this:
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE({Level of Influence}, “Very Low”,“1”),
“Low”,“2”),
“Medium”,“3”),
“High”, “4”),
“Very High”,“5”)
The results that are returned work unless you select “Very High”. It shows up as “Very 4” rather than “5” in the column with the new number value. I can’t figure out why the first four work fine and the last doesn’t. It looks like the substitute formula is replacing the “High” in “Very High” with the “4”
Can someone help me figure out how to fix this?
