Extracting Numeric Value from Text

[Philip_Barber]

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

[Philip_Barber]

augmented:

VALUE(RIGHT({No of Weeks},LEN({No of Weeks})- FIND("$",{No of Weeks})-1))

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.

[kuovonne]

Philip_Barber:

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.

The place to change the currency symbol was in the formula field, so the formula would have the symbol in your single-select field.

Philip_Barber:

I then tried the SWITCH solution that worked but introduced a different problem

Check if your formula has curly quotes “ ” and replace them with straight quotes "

[Philip_Barber]

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.

[augmented]

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})))

[Philip_Barber]

augmented:

VALUE(RIGHT({No of Weeks},LEN({No of Weeks})- FIND(“£”,{No of Weeks})))

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.

[Philip_Barber]

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?

[augmented]

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.

[Philip_Barber]

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

[Justin_Barrett]

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)

[Philip_Barber]

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