| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to extract decimal numbers e.g. $1.57 from alphanumeric string
Hi!
Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) But this only returns the first whole number. Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract decimal numbers e.g. $1.57 from alphanumeric string
Try this:
=LOOKUP(100^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:"&LEN(A1))))) Biff "Lio" wrote in message ... Hi! Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) But this only returns the first whole number. Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract decimal numbers e.g. $1.57 from alphanumeric string
Hi Lio,
Try: =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))) Cheers -- macropod [MVP - Microsoft Word] "Lio" wrote in message ... Hi! Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW( $1:$9),1))) But this only returns the first whole number. Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract decimal numbers e.g. $1.57 from alphanumeric string
Formula got munged. The second FIND should be FIND(" " ....
=LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))) Cheers -- macropod [MVP - Microsoft Word] "macropod" wrote in message ... Hi Lio, Try: =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))) Cheers -- macropod [MVP - Microsoft Word] "Lio" wrote in message ... Hi! Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW( $1:$9),1))) But this only returns the first whole number. Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract decimal numbers e.g. $1.57 from alphanumeric string
Nel post
*Lio* ha scritto: Hi! Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) But this only returns the first whole number. Thanks. If the string has always the same structure you could use this formula: =VALUE(MID(B8,FIND("$",B8)+1,5)) where in B8 you have your string. Then you have to format as Currency the cell with the formula. -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract decimal numbers e.g. $1.57 from alphanumeric string
You're picking up the space after the number. Also, to convert the string to
a number: =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)), FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))-1)+0 However, this fails on strings like: Share price is $22.09 It works with this modification: =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)), FIND(" ",RIGHT(A1&" ",LEN(A1&" ")-FIND("$",A1)))-1)+0 Biff "macropod" wrote in message ... Formula got munged. The second FIND should be FIND(" " .... =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))) Cheers -- macropod [MVP - Microsoft Word] "macropod" wrote in message ... Hi Lio, Try: =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))) Cheers -- macropod [MVP - Microsoft Word] "Lio" wrote in message ... Hi! Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW( $1:$9),1))) But this only returns the first whole number. Thanks. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract decimal numbers e.g. $1.57 from alphanumeric string
Hi Biff,
True, but the OP suggested the string includes 'per share'. In any event, I think Franz's solution is the best so far. Cheers -- macropod [MVP - Microsoft Word] "T. Valko" wrote in message ... You're picking up the space after the number. Also, to convert the string to a number: =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)), FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))-1)+0 However, this fails on strings like: Share price is $22.09 It works with this modification: =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)), FIND(" ",RIGHT(A1&" ",LEN(A1&" ")-FIND("$",A1)))-1)+0 Biff "macropod" wrote in message ... Formula got munged. The second FIND should be FIND(" " .... =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))) Cheers -- macropod [MVP - Microsoft Word] "macropod" wrote in message ... Hi Lio, Try: =LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))) Cheers -- macropod [MVP - Microsoft Word] "Lio" wrote in message ... Hi! Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW( $1:$9),1))) But this only returns the first whole number. Thanks. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract decimal numbers e.g. $1.57 from alphanumeric st
Hi Franz, Biff & Macropod!
Thank you very much to all three of you! I'm trying out getting help from the community the very first time and I'm amazed by the speed and willingness to help!! Whilst Franz's solution is the most elegant to my given problem, in other situations where the string structure is not constant (different decimal places or missing currency sign $), Macropod's and Biff's respective solutions work perfectly. Great job guys!! Lionel "Franz Verga" wrote: Nel post *Lio* ha scritto: Hi! Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) But this only returns the first whole number. Thanks. If the string has always the same structure you could use this formula: =VALUE(MID(B8,FIND("$",B8)+1,5)) where in B8 you have your string. Then you have to format as Currency the cell with the formula. -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract decimal numbers e.g. $1.57 from alphanumeric st
You're welcome!
Biff "Lio" wrote in message ... Hi Franz, Biff & Macropod! Thank you very much to all three of you! I'm trying out getting help from the community the very first time and I'm amazed by the speed and willingness to help!! Whilst Franz's solution is the most elegant to my given problem, in other situations where the string structure is not constant (different decimal places or missing currency sign $), Macropod's and Biff's respective solutions work perfectly. Great job guys!! Lionel "Franz Verga" wrote: Nel post *Lio* ha scritto: Hi! Alphanumeric string: "Option Exercise at $22.09 per share." How does one extract 22.09 from the above alphanumeric string? I tried the array: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) But this only returns the first whole number. Thanks. If the string has always the same structure you could use this formula: =VALUE(MID(B8,FIND("$",B8)+1,5)) where in B8 you have your string. Then you have to format as Currency the cell with the formula. -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to extract decimal numbers from alphanumeric strings in Excel | Excel Discussion (Misc queries) | |||
| Function to extract numbers from an alphanumeric cell | Excel Worksheet Functions | |||
| how to loose all numbers after decimal (not rounding) | Excel Worksheet Functions | |||
| extract numbers from cell problem | Excel Worksheet Functions | |||
| How do you extract numbers from a string of chacters in a cell (E. | Excel Worksheet Functions | |||
Linear Mode
