Help

Extracting Numeric Value from Text

4925 22
cancel
Showing results for 
Search instead for 
Did you mean: 
Philip_Barber
7 - App Architect
7 - App Architect

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

Screenshot 2020-12-03 at 11.25.41

22 Replies 22
augmented
10 - Mercury
10 - Mercury

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

Philip_Barber
7 - App Architect
7 - App Architect

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
Screenshot 2020-12-03 at 22.40.50

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
)

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.

Philip_Barber
7 - App Architect
7 - App Architect

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.
Screenshot 2020-12-04 at 13.51.47

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.
Screenshot 2020-12-04 at 14.42.40

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?

Screenshot 2020-12-04 at 16.51.06

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)
Philip_Barber
7 - App Architect
7 - App Architect

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