Grabing a number from a non-number string.

Question

I need to get the number from a non-numeric sting so that i can us it in a formula to be able to show unit price for example 4 of x = 1,000.00. I need to grab that number 4 out of the text and use it in the simple formula to show that the unit price of each is 250.00. Any suggestions please?

Answer 1

This can depend on whether the structure of the phrase is standard or not.

Regex (perl-style):

/\D(\d*)\D/

should match any string of numbers surrounded by things other than numbers.

/\b(\d*)\b/

is safer since it will ensure it isn't part of a word, name, or model/serial number.

If you're expecting commas/decimals, you'll need to make it more like:

/\b((\d|\.|,)*)\b/

If on the other hand you know the numbers are at the start and end of the string, and you're avoiding regex for some reason, you could just call split or explode on the string, and grab the first and last parts.

Answer 2

If it is always going to be "A of x = B" then you can probably something like the below as a quick and dirty solution:

VAL(Left({Test;1.testfield}, IIF(Instr({Test;1.testfield}, " x = ") > 0, Instr({Test;1.testfield}, " x = ") - 1, 0)))

This gets the section before the " x = " and converts it to a number. I added the IIF just in case this string doesn't exist, but this may not be necessary.