Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 16, 2021 03:54 PM
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.
Solved! Go to Solution.
Jun 16, 2021 04:05 PM
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.
Jun 16, 2021 04:05 PM
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.
Jun 17, 2021 05:01 AM
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?
Jun 17, 2021 09:02 AM
You can’t sum up multiple rows within Airtable unless those rows are somehow linked together, or all linked to one common row.