Thread: 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" <ctaca@hotmail.com> wrote in message
news:ucL4j5PFFHA.3384@tk2msftngp13.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:evKk%23$PFFHA.624@TK2MSFTNGP15.phx.gbl...
> Hi Stan,
>
> One way
>
> =--RIGHT(D4,2)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Stan Altshuller" <ctaca@hotmail.com> wrote in message
> news:ucL4j5PFFHA.3384@tk2msftngp13.phx.gbl...
> > 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" <ctaca@hotmail.com> wrote in message
news:e%23dN1CQFFHA.2176@TK2MSFTNGP15.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:evKk%23$PFFHA.624@TK2MSFTNGP15.phx.gbl...
> > Hi Stan,
> >
> > One way
> >
> > =--RIGHT(D4,2)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Stan Altshuller" <ctaca@hotmail.com> wrote in message
> > news:ucL4j5PFFHA.3384@tk2msftngp13.phx.gbl...
> > > 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" <ctaca@hotmail.com> wrote in message
news:e%23dN1CQFFHA.2176@TK2MSFTNGP15.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:evKk%23$PFFHA.624@TK2MSFTNGP15.phx.gbl...
> > Hi Stan,
> >
> > One way
> >
> > =--RIGHT(D4,2)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Stan Altshuller" <ctaca@hotmail.com> wrote in message
> > news:ucL4j5PFFHA.3384@tk2msftngp13.phx.gbl...
> > > 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" <ctaca@hotmail.com> wrote in message
news:ucL4j5PFFHA.3384@tk2msftngp13.phx.gbl...
> 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" <ctaca@hotmail.com>
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" <bob.phillips@notheretiscali.co.uk> wrote in message
>news:evKk%23$PFFHA.624@TK2MSFTNGP15.phx.gbl...
>> Hi Stan,
>>
>> One way
>>
>> =--RIGHT(D4,2)
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Stan Altshuller" <ctaca@hotmail.com> wrote in message
>> news:ucL4j5PFFHA.3384@tk2msftngp13.phx.gbl...
>> > 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
>> >
>> >
>> >
>>
>>
>