Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Lio Lio is offline
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 329
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 329
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 459
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 329
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Lio Lio is offline
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to extract decimal numbers from alphanumeric strings in Excel Old Tone Excel Discussion (Misc queries) 13 March 23rd 06 04:49 PM
Function to extract numbers from an alphanumeric cell diana Excel Worksheet Functions 4 March 13th 06 10:55 AM
how to loose all numbers after decimal (not rounding) arjay9 Excel Worksheet Functions 3 January 13th 06 02:00 AM
extract numbers from cell problem bill gras Excel Worksheet Functions 5 October 21st 05 01:46 PM
How do you extract numbers from a string of chacters in a cell (E. blackbeemer Excel Worksheet Functions 6 November 12th 04 10:00 AM


All times are GMT +1. The time now is 06:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"