Extract number from text/number string..

G

Guest

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

Dave Peterson

<b><i>
is no longer 3 characters.


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

Ron Rosenfeld

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
 
G

Guest

Thanks Dave, that worked:
came up with the following, let me know if see anything wrong

=IF(CD9="","",IF(LEFT(CD9,3)<>"<i>",
--MID(LEFT(CD9,LEN(CD9)-4),SEARCH("<b>",CD9)+3,255),
--MID(LEFT(CD9,LEN(CD9)-8),SEARCH("<b><i>",CD9)+6,255)))

works for both:
11:35am - <b>0.5601</b> <i>11:19am</i> - <b><i>1111.16</i></b>

for others, was working on extracting number from variations:
<i>11:19am</i> - <b><i>0.16</i></b>
11:35am - <b>0.5601</b>
11:35am - <b>10.56</b>
11:35am - <b>1111.5601</b>
 
G

Guest

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

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top