Formula to replace/substitute certain numbers into another number

[Red_Fox]

We´re a learn platform with memberships. Each mempberships has its values in numbers.

I need to replace/substitute those values into another values.

This is what I want:

9=1
19=2
39=4
99=11

This is my formula:
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({FOX XP AMOUNT},“9”,“1”),“19”,“2”),“39”,“4”),“99”,“11”)

This is what I got:

I think I need a solution to get the Formula know or read two digit numbers together. But I don´t know how. Would love if somebody could help me please.

[Kamille_Parks · Accepted solution]

First: should {FOX XP AMOUNT} be an actual Number field, instead of a SingleLineText field, if all the values are numbers?

Secondly, your formula would work better using a single SWITCH() function than several SUBSTITUTES() if, based on what you’ve shown, you aren’t replacing a portion of the value but the whole thing:

SWITCH(
   VALUE({FOX XP AMOUNT}),
   9, 1,
   19, 2,
   39, 4,
   99, 11,
   {FOX XP AMOUNT}
)

^ The above formula uses VALUE() to convert the amount to a number, and the last item in the SWITCH() defaults to the amount if none of your listed replacements apply.

[Red_Fox]

Thank you so much for the fast reply and the given solution! :slightly_smiling_face:

I changed the origin column into numbers and took this formula:

SWITCH(
   {FOX XP AMOUNT},
   9, 1,
   19, 2,
   39, 4,
   99, 11,
   {FOX XP AMOUNT}
)

It worked! Thank you.

Now I stuppon into another problem. I would like to sum up the column “FOX XP VALUE” into one single row. So that I can use that number as an value to transfer it to my Webflow CMS.

Should I open another topic or can you help me in here?

[Kamille_Parks]

You can’t sum up multiple rows within Airtable unless those rows are somehow linked together, or all linked to one common row.