Extracting Numeric Value from Text

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.

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 ?

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

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

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
)

SWITCH({No of weeks},
  "5 to half term £40", 40,
  "11 for full term £80", 80
)

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.

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!

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.

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.

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.

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 "

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.

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

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.

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?

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.

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

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)

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

IF({Field Name}, ...

IF({No of Weeks}, VALUE(RIGHT({No of Weeks}, LEN({No of Weeks})- FIND("£", {No of Weeks}))), 0)

The formula works a treat however I have made an error and not included details of a numeric field called CHILDREN.
So far the formula has ignored this field and I have got the correct answer if only one child has booked however if there is more than one child then there is a discounted rate of £5 for every child for every week in addition to the original charge.

I am sure it is possible to extend the formula however I have no idea as to how.
Would you be so kind to extend the formula for me to take into account additional children.

I should also note that I have had to separate the NO OF WEEKS so that I have NO OF WEEKS FM and also NO OF WEEKS S&R
I have done this to make the booking form specific for the Class as otherwise it was giving too many options and conditions.

I am definitely feeling embarrassed even to ask but I have now added a Zoom booking and also a PARTY booking that I would like added to Amount. My niece is thanking me so much for changing her from manual record and I keep telling her the difficult bits are being done in America! Thanking you in advance.
Phil

Sorry for the delay getting back to this. Including the extra fields will actually be fairly straightforward. The part that’s confusing me a little is this:

I want to make sure that I understand the logic. If it’s an 11-week course, and there’s only one child, it’s £70. If there are two children, that’s another £5 x 11 (£55), for a grand total of £125. Is that correct? Or does that £5/child discount rate also include the first child, not just the second, meaning the total would be £110, not £125?

From what I gather, that discount only applies to selections coming from {No of weeks FM} and {No of weeks S&R}. Is that correct? The other fields—{Party} and {Zoom}—don’t appear to be based on a number of weeks, so it feels like they should be excluded from this extra-children calculation.

Hi Justin and thanks for continuing to show an interest in my problem.
I have implemented the solution given to me by “Augmented” shame I don’t actually know his name.

It is only the second (and subsequent) child that the discount is applied…

For your information my booking form does not give a user the chance of putting in numbers of children on a Zoom call, as they are not charged in that way.

I created a separate Party Children Number for information only as a party is a totally different event to a class as it is charged by length of party time not numbers.

I think I am now sorted however over the weekend I will be going through how it all works with my niece and hopefully I have understood her problems and (with your help) given her the answer to her needs.

Thanks again
Phil