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.
Best answer by Kamille_Parks11
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.
+27First: 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.
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?
+27Thank 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?
You can’t sum up multiple rows within Airtable unless those rows are somehow linked together, or all linked to one common row.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.