Extract numbers from strings
 

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

Hi Stan,

One way

=--RIGHT(D4,2)

--

HTH

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


"Stan Altshuller" wrote in message
...
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

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" wrote in message
...
Hi Stan,

One way

=--RIGHT(D4,2)

--

HTH

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


"Stan Altshuller" wrote in message
...
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

--

HTH

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


"Stan Altshuller" wrote in message
...
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" wrote in message
...
Hi Stan,

One way

=--RIGHT(D4,2)

--

HTH

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


"Stan Altshuller" wrote in message
...
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

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)


"Stan Altshuller" wrote in message
...
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" wrote in message
...
Hi Stan,

One way

=--RIGHT(D4,2)

--

HTH

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


"Stan Altshuller" wrote in message
...
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

=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
--
Dana DeLouis
Win XP & Office 2003

"Stan Altshuller" wrote in message
...
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

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)


On Thu, 17 Feb 2005 09:55:09 -0500, "Stan Altshuller"
wrote:

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" wrote in message
...
Hi Stan,

One way

=--RIGHT(D4,2)

--

HTH

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


"Stan Altshuller" wrote in message
...
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