Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# Formula to replace/substitute certain numbers into another number

Topic Labels: Formulas
Solved
711 3
cancel
Showing results for
Did you mean:
4 - Data Explorer

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.

1 Solution

Accepted Solutions
16 - Uranus

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.

3 Replies 3
16 - Uranus

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.

4 - Data Explorer

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?

16 - Uranus

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