Extracting Numeric Value from Text

Philip_Barber · ‎Dec 03, 2020

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

augmented · ‎Dec 03, 2020

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.

Philip_Barber · ‎Dec 03, 2020

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 ?

augmented · ‎Dec 03, 2020

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 · ‎Dec 03, 2020

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

kuovonne · ‎Dec 03, 2020

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
)

augmented · ‎Dec 03, 2020

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.

kuovonne · ‎Dec 03, 2020

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!

Philip_Barber · ‎Dec 04, 2020

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.

augmented · ‎Dec 04, 2020

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.