ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   "Extract" specific values from cells (https://www.excelbanter.com/excel-discussion-misc-queries/78665-extract-specific-values-cells.html)

Portuga
"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


--
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
"Extract" specific values from cells
 
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

Portuga
"Extract" specific values from cells
 

Brill!

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

Thanks!


--
Portuga
------------------------------------------------------------------------
Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385
View this thread: http://www.excelforum.com/showthread...hreadid=524820

Ron Rosenfeld
"Extract" specific values from cells
 
On Tue, 21 Mar 2006 09:37:51 -0600, 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

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


All times are GMT +1. The time now is 05:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com