Extract number from text/number string..

thank you very much, will check that out. see above for what was working
on.. thanks..

"Ron Rosenfeld" wrote:

> On Wed, 5 Jul 2006 11:49:01 -0700, nastech <nastech@discussions.microsoft.com>
> wrote:
>
> >060705 Extract number from text/number string..
> >
> >is there a way to make the following work? thanks
> >
> >=IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-8),SEARCH("<b><i>",CC9)+3,255))
> >
> >to extract: 0.16 from: (formula above gets #Value error)
> ><i>11:19am</i> - <b><i>0.16</i></b>
> >
> >----------
> >other variation, have formula that works for different input:
> >=IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b>",CC9)+3,255))
> >
> >11:35am - <b>0.5601</b>
>
> Depending on the criteria used to define "number", you could do it with Regular
> Expressions.
>
> At it's simplest, if the number you wish to extract will always have
>
> At least one digit
> Decimal point
> At least one digit
>
> and there will be no other similar sequences prior to the number you wish to
> extract, the regular expression "\d+\.\d+" would define it.
>
> You can use Regular Expressions in Excel by downloading and installing Longre's
> free morefunc.xll add-in from http://xcell05.free.fr/
>
> Then use the formula:
>
> =REGEX.MID(A1,"\d+\.\d+")
>
> If the possibilities for the number are more complex, then a Regular Expression
> can be crafted to take that into account.
>
>
> --ron
>

On Wed, 5 Jul 2006 13:02:01 -0700, nastech <nastech@discussions.microsoft.com>
wrote:

>thank you very much, will check that out. see above for what was working
>on.. thanks..

The formula I posted works on all those examples, for what it's worth.


--ron