Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Formula to replace/substitute certain numbers into another number

Topic Labels: Formulas
Solved
Jump to Solution
2016 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Red_Fox
4 - Data Explorer
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

image

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

This is what I got:

image

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
Kamille_Parks
16 - Uranus
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.

See Solution in Thread

3 Replies 3
Kamille_Parks
16 - Uranus
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.

Red_Fox
4 - Data Explorer
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}
)

image

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?

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