Dec 03, 2020 03:37 AM
I have a lookup option (in Number of weeks) that has two different values. I want to be able to extract the numeric value from either of the text strings that is chosen and place that value in the AMOUNT column but I have no real idea as to how to do it. Can somebody please help me
Dec 03, 2020 11:11 AM
Philip, this suggestion is un-elegant at best. Change the Amount column to a Formula field. Put this formula inside, replacing $ with £
VALUE(RIGHT({No of Weeks},LEN({No of Weeks})- FIND("$",{No of Weeks})))
Then you should be able to format the Amount field as your desired currency.
Dec 03, 2020 11:38 AM
Thanks for looking.
Your answer is far closer than I have got but regrettable it is not correct.
The figure it returns is all the numeric values, so for example, rather than £40 it gives me the answer of £540
Can the formula be tweaked ?
Dec 03, 2020 12:02 PM
It doesn’t return all of the numbers in the text, it just looks like it does. There must be something going on with the GBP currency symbol and the FIND function. What happens when you change the formula to the following?
VALUE(RIGHT({No of Weeks},LEN({No of Weeks})- FIND("$",{No of Weeks})-1))
Dec 03, 2020 02:45 PM
Thanks again for looking I am truly lost even looking at the formula.
It made the first example correct but when the second number contains two digits it only removed one of them. That doesn’t sound at all clear so I will do a screen shot
Dec 03, 2020 04:03 PM
You are using different currency symbols. The formula is looking for the $
currency symbol, but your field uses the £
currency symbol. Try replacing the currency symbol in the formula.
On the other hand, since the {No of weeks} field is a single select field, why not use a SWITCH
statement instead of trying to parse the string?
SWITCH({No of weeks},
"5 to half term £40", 40,
"11 for full term £80", 80
)
Dec 03, 2020 06:36 PM
I’m pretty sure I said to replace the $ with the GBP symbol. I see that I failed to remind Philip in my second response. I would be surprised if it worked in any recognizable way if he had not changed the search character.
Your SWITCH suggestion means changing the formula every time you add a new item to the single select, no? I would rather parse.
Dec 03, 2020 07:47 PM
Yes, you did. The failure to replace the symbol is why he is getting the results he got. Since his values didn’t contain the $, the RIGHT
function is taking all of the characters, instead of just the ones to the right of the currency symbol.
True. There are many ways to do things with code!
Dec 04, 2020 01:13 AM
I woke up this morning excited to see if my problem had been solved and yes it has however it has created a new one.
Firstly can I say that I had changed to the £ sign in the formatting. Not sure if I was meant to change it anywhere else but we will leave it there.
I then tried the SWITCH solution that worked but introduced a different problem as I had to add the option for the booking form to contain just a single (1) class.
I tried to add another line to your formula
SWITCH({No of weeks},
"1 week £10”, 10,
“5 to half term £40”, 40,
“11 for full term £80”, 80
)
But that produced an error so would you be so kind to tell me how I can overcome this problem.
I can’t thank you both enough for your efforts on my behalf.
Dec 04, 2020 05:29 AM
Hi Philip. If you were to change the “$” to the “£” in the original formula that I suggested, it should work. I’m not sure what would cause the error in Kuovonne’s formula, but it is trying to do an exact string match in the SWITCH statement. So, make sure you don’t have extra spaces in your new select item. Good luck.
Dec 04, 2020 05:56 AM
My apologies as I was in the past changing the £ sign in the formatting and not in the formula.
I have now done as you suggested but regrettable it shows the following.
So I guess I am still doing something wrong.
Dec 04, 2020 06:21 AM
The place to change the currency symbol was in the formula field, so the formula would have the symbol in your single-select field.
Check if your formula has curly quotes “ ”
and replace them with straight quotes "
Dec 04, 2020 06:45 AM
It was a challenge for me to even find out how to put in straight quotes but I have now managed that.
The formula saved but there were no values showing in the amount column and “error” showing in all the blank amount fields.
Success must be so close but I have no idea what to do.
Thank you for helping.
Dec 04, 2020 08:06 AM
At least for my suggestion, try this Philip. It’s just my original suggestion with the £ in the right place.
VALUE(RIGHT({No of Weeks},LEN({No of Weeks})- FIND(“£”,{No of Weeks})))
Dec 04, 2020 08:38 AM
My machine automatically converts your straight quotes to curly ones so it has taken me time to do as you said.
The result is perfect, it even handled the one week option that I had added.
I would like to thank you both for your time and effort for something that was way beyond my knowledge.
I can now progress on the other part of the Airtable base and hope that I can make the other things work myself.
Thanks once again and sorry for not putting the £ sign in correctly when first asked.
Dec 04, 2020 09:08 AM
Following the success of the formula I started a new database without any records and made a booking.
In my example the adult can pay for a series and then in following weeks just confirm the date they are coming and not put anything into the payment as they have already paid.
The problem is that if the field is left blank it shows an error.
Is there anyway to cater for this scenario?
Dec 04, 2020 10:15 AM
Replace your formula with this one…
IF(LEN(TRIM({No of Weeks}))=0,0,VALUE( RIGHT({No of Weeks},LEN({No of Weeks})- FIND(“£”,{No of Weeks}))))
It will show 0 when your single select is empty.
Dec 04, 2020 11:19 AM
Thank you again it does exactly what I require.
I am having a problem with another part of this database but I am going to try and do it a different way without bothering you again.
Many thanks
Phil
Dec 04, 2020 11:20 AM
That could be simplified a bit by using the shortcut to check for any contents in a field:
IF({Field Name}, ...
In this case:
IF({No of Weeks}, VALUE(RIGHT({No of Weeks}, LEN({No of Weeks})- FIND("£", {No of Weeks}))), 0)
Dec 04, 2020 11:48 AM
It is now 8pm Friday night and I have been looking at the screen for too long today. I am out tomorrow but I will try this next week.
Thank you for your contribution on something that I have no idea about.
Regards
Phil