Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number from text/number string..
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) <i11:19am</i - <b<i0.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 - <b0.5601</b |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number from text/number string..
<b<i
is no longer 3 characters. nastech 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) <i11:19am</i - <b<i0.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 - <b0.5601</b -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number from text/number string..
On Wed, 5 Jul 2006 11:49:01 -0700, nastech
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) <i11:19am</i - <b<i0.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 - <b0.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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number from text/number string..
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 - <b0.5601</b <i11:19am</i - <b<i1111.16</i</b for others, was working on extracting number from variations: <i11:19am</i - <b<i0.16</i</b 11:35am - <b0.5601</b 11:35am - <b10.56</b 11:35am - <b1111.5601</b "Dave Peterson" wrote: <b<i is no longer 3 characters. nastech 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) <i11:19am</i - <b<i0.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 - <b0.5601</b -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 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) <i11:19am</i - <b<i0.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 - <b0.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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number from text/number string..
On Wed, 5 Jul 2006 13:02:01 -0700, nastech
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling Data off Web - Need Function Help | Excel Worksheet Functions | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
EXTRACT NUMBERS FROM TEXT STRING | Excel Worksheet Functions | |||
Calculating number of letters in a string ... | Excel Discussion (Misc queries) | |||
Calculating number of letters in a string ... | Excel Worksheet Functions |