How to extract decimal numbers e.g. $1.57 from alphanumeric string

[Guest]

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.

 

[T. Valko]

Try this:

=LOOKUP(100^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Biff

 

[macropod]

Hi Lio,

Try:
=LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND("
",RIGHT(A1,LEN(A1)-FIND("$",A1))))

Cheers

 

[macropod]

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]

Hi Lio,

Try:
=LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND("
",RIGHT(A1,LEN(A1)-FIND("$",A1))))

Cheers

--
macropod
[MVP - Microsoft Word]

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.

 

[Franz Verga]

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

 

[T. Valko]

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

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]

Hi Lio,

Try:
=LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND("
",RIGHT(A1,LEN(A1)-FIND("$",A1))))

Cheers

--
macropod
[MVP - Microsoft Word]

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.

 

[macropod]

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]

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

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]

Hi Lio,

Try:
=LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND("
",RIGHT(A1,LEN(A1)-FIND("$",A1))))

Cheers

--
macropod
[MVP - Microsoft Word]


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.

 

[T. Valko]

You're welcome!

Biff

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