"Extract" specific values from cells

Hi,

I have the (partial) list (bellow) in column A, pasted from another file.
I need to either extract from this list the values that start with "R" and the numbers that follow it:

R157
R246
R39
etc,
etc,


Or delete from the list anything else that is not:

R157
R246
R39
etc,
etc,

Any easy way of doing this?
Thanks

List:

if (leftstr(input7,7) = "R157_inc")then
if (leftstr(input8,7) = "R246_inc")then
if (leftstr(input9,7) = "R39_inc")then
if (leftstr(input10,7) = "R27_inc")then
if (leftstr(input11,7) = "R21_inc")then
if (leftstr(input12,7) = "R112_bad")then
if (leftstr(input13,7) = "R271_bad")then
if (leftstr(input14,8) = "R331_bad")then
if (leftstr(input15,7) = "R80_bad")then
if (leftstr(input16,8) = "R272_bad")then
if (leftstr(input17,8) = "R270_bad")then
if (leftstr(input18,8) = "R341_bad")then
if (leftstr(input19,8) = "R330_bad")then
if (leftstr(input20,8) = "R323_bad")then
if (leftstr(input21,7) = "R34_ina")then
if (leftstr(input22,7) = "R12_ina")then
if (leftstr(input23,7) = "R26_ina")then
if (leftstr(input24,7) = "R49_inc")then
if (leftstr(input25,8) = "R357_inc")then
if (leftstr(input26,8) = "R353_non")then
if (leftstr(input27,8) = "R354_non")then
if (leftstr(input28,8) = "R351_non")then
if (leftstr(input29,6) = "R8_non")then
if (leftstr(input30,6) = "R6_non")then
if (leftstr(input31,6) = "R5_non")then
if (leftstr(input32,7) = "R28_non")then
if (leftstr(input33,8) = "R345_non")then
if (leftstr(input34,7) = "R40_non")then
if (leftstr(input35,7) = "R416_non")then
if (leftstr(input36,7) = "R418_inc")then
if (leftstr(input37,7) = "R420_inc")then
if (leftstr(input38,7) = "R422_inc")then
if (leftstr(input39,7) = "R17_inc")then
if (leftstr(input40,7) = "R14_inc")then
if (leftstr(input41,7) = "R101_inc")then
if (leftstr(input42,6) = "R1_non")then
if (leftstr(input43,8) = "R127_bad")then
if (leftstr(input44,7) = "R92_bad")then

Select the range
Edit|replace
what: _* (Underscore then asterisk)
with: (leave blank)
replace all

You'll be left with strings like:
if (leftstr(input7,7) = "R157
if (leftstr(input8,7) = "R246
if (leftstr(input9,7) = "R39
if (leftstr(input10,7) = "R27

Then one more
Edit|replace
what: *" (asterisk then double quote)
with: (leave blank)
replace all


Portuga wrote:
>
> Hi,
>
> I have the (partial) list (bellow) in column A, pasted from another
> file.
> I need to either extract from this list the values that start with "R"
> and the numbers that follow it:
>
> R157
> R246
> R39
> etc,
> etc,
>
> Or delete from the list anything else that is not:
>
> R157
> R246
> R39
> etc,
> etc,
>
> Any easy way of doing this?
> Thanks
>
> LIST:
>
> if (leftstr(input7,7) = "R157_inc")then
> if (leftstr(input8,7) = "R246_inc")then
> if (leftstr(input9,7) = "R39_inc")then
> if (leftstr(input10,7) = "R27_inc")then
> if (leftstr(input11,7) = "R21_inc")then
> if (leftstr(input12,7) = "R112_bad")then
> if (leftstr(input13,7) = "R271_bad")then
> if (leftstr(input14,8) = "R331_bad")then
> if (leftstr(input15,7) = "R80_bad")then
> if (leftstr(input16,8) = "R272_bad")then
> if (leftstr(input17,8) = "R270_bad")then
> if (leftstr(input18,8) = "R341_bad")then
> if (leftstr(input19,8) = "R330_bad")then
> if (leftstr(input20,8) = "R323_bad")then
> if (leftstr(input21,7) = "R34_ina")then
> if (leftstr(input22,7) = "R12_ina")then
> if (leftstr(input23,7) = "R26_ina")then
> if (leftstr(input24,7) = "R49_inc")then
> if (leftstr(input25,8) = "R357_inc")then
> if (leftstr(input26,8) = "R353_non")then
> if (leftstr(input27,8) = "R354_non")then
> if (leftstr(input28,8) = "R351_non")then
> if (leftstr(input29,6) = "R8_non")then
> if (leftstr(input30,6) = "R6_non")then
> if (leftstr(input31,6) = "R5_non")then
> if (leftstr(input32,7) = "R28_non")then
> if (leftstr(input33,8) = "R345_non")then
> if (leftstr(input34,7) = "R40_non")then
> if (leftstr(input35,7) = "R416_non")then
> if (leftstr(input36,7) = "R418_inc")then
> if (leftstr(input37,7) = "R420_inc")then
> if (leftstr(input38,7) = "R422_inc")then
> if (leftstr(input39,7) = "R17_inc")then
> if (leftstr(input40,7) = "R14_inc")then
> if (leftstr(input41,7) = "R101_inc")then
> if (leftstr(input42,6) = "R1_non")then
> if (leftstr(input43,8) = "R127_bad")then
> if (leftstr(input44,7) = "R92_bad")then
>
> --
> Portuga
> ------------------------------------------------------------------------
> Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385
> View this thread: http://www.excelforum.com/showthread...hreadid=524820

--

Dave Peterson

Brill!

Wasnt aware that you could use the * like that in the find and replace.

Thanks!

On Tue, 21 Mar 2006 09:37:51 -0600, Portuga
<Portuga.2510km_1142955601.2314@excelforum-nospam.com> wrote:

>
>Hi,
>
>I have the (partial) list (bellow) in column A, pasted from another
>file.
>I need to either extract from this list the values that start with "R"
>and the numbers that follow it:
>
>R157
>R246
>R39
>etc,
>etc,
>
>
>Or delete from the list anything else that is not:
>
>R157
>R246
>R39
>etc,
>etc,
>
>Any easy way of doing this?
>Thanks
>
>LIST:
>
>if (leftstr(input7,7) = "R157_inc")then
>if (leftstr(input8,7) = "R246_inc")then
>if (leftstr(input9,7) = "R39_inc")then
>if (leftstr(input10,7) = "R27_inc")then
>if (leftstr(input11,7) = "R21_inc")then
>if (leftstr(input12,7) = "R112_bad")then
>if (leftstr(input13,7) = "R271_bad")then
>if (leftstr(input14,8) = "R331_bad")then
>if (leftstr(input15,7) = "R80_bad")then
>if (leftstr(input16,8) = "R272_bad")then
>if (leftstr(input17,8) = "R270_bad")then
>if (leftstr(input18,8) = "R341_bad")then
>if (leftstr(input19,8) = "R330_bad")then
>if (leftstr(input20,8) = "R323_bad")then
>if (leftstr(input21,7) = "R34_ina")then
>if (leftstr(input22,7) = "R12_ina")then
>if (leftstr(input23,7) = "R26_ina")then
>if (leftstr(input24,7) = "R49_inc")then
>if (leftstr(input25,8) = "R357_inc")then
>if (leftstr(input26,8) = "R353_non")then
>if (leftstr(input27,8) = "R354_non")then
>if (leftstr(input28,8) = "R351_non")then
>if (leftstr(input29,6) = "R8_non")then
>if (leftstr(input30,6) = "R6_non")then
>if (leftstr(input31,6) = "R5_non")then
>if (leftstr(input32,7) = "R28_non")then
>if (leftstr(input33,8) = "R345_non")then
>if (leftstr(input34,7) = "R40_non")then
>if (leftstr(input35,7) = "R416_non")then
>if (leftstr(input36,7) = "R418_inc")then
>if (leftstr(input37,7) = "R420_inc")then
>if (leftstr(input38,7) = "R422_inc")then
>if (leftstr(input39,7) = "R17_inc")then
>if (leftstr(input40,7) = "R14_inc")then
>if (leftstr(input41,7) = "R101_inc")then
>if (leftstr(input42,6) = "R1_non")then
>if (leftstr(input43,8) = "R127_bad")then
>if (leftstr(input44,7) = "R92_bad")then

Here is a formula to extract the Rnnn type strings. I am assuming that the
first capital R in the string will be the one you are looking for. If not,
post back.

First download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=REGEX.MID(A1,"R\d+")


--ron