Extract numbers from strings

[Stan Altshuller]

Hi All!


How do I extract a number from a string? Example:
cell D64 has a string "Q30 no lockup" in my case, the first char is always
a letter and the next two chars form a number I need to extract.
When I try
=N(right(left(D64,3),2)) it returns 0 I need 30. I need ultimately to find
the average of these numbers in the strings running across.
Help?

THANKS!
Stan

 

[Bob Phillips]

Hi Stan,

One way

=--RIGHT(D4,2)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Stan Altshuller]

it works!
how would one know that? I can not find help on this -- function. amazing
anyway THANKS for replying so quickly.

Stan

 

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

It's the RIGHT that does the work, the -- just ensures it is a number. You
can get the same result with

=VALUE(RIGHT(D4,2))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dana DeLouis]

=N(right(left(D64,3),2))

Instead of using Right & Left, using Mid might be an option:

The following would return 30 from "Q30 no lockup"

=VALUE(MID(A1,2,2))
or:
=--(MID(A1,2,2))

HTH

 

[Myrna Larson]

The function that is intended for this sort of thing (i.e. you know where the
text begins (2nd char) and how long it is (2 chars) is MID.

=--MID(D4,2,2)