Need help for formula to extract number...
abc de/f 123456 123.45/678.90 - extract 123.45
abcd 4.5% 456789 12345.67/23456.78 - extract 12345.67
abcd e 123789 abc1234 - nothing to extract, show 0
Basically, it will always be 123/456, then extract 123 before the
slash.
Formula needed. Thanks.
Easiest (for me) solution is to use Regular Expressions.
First, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/
Then use this formula:
=REGEX.MID(A1,"[.\d]+(?=/)")
This formula will extract the numbers as a text string, and show <blank> if
there is nothing to extract. The expression "[.\d]+(?=/)" says
extract the first sequence of dots (.) and digits that ends with a
<slash>, but don't return the <slash>.
So it works with examples of the type you give, but would also extract, for
example, an IP address that ended with a slash.
There is a requirement that the "digit dot" string ends with a slash, but no
requirement that the slash is followed by another digit. If that might cause a
problem in your extractions, it is easily remedied.
If you need the numbers as values (rather than strings), and a <zero> if there
is nothing to extract, then I believe this will work:
=--REGEX.MID(A1&"0/","[.\d]+(?=/)")
--ron
