Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > "Extract" specific values from cells
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Portuga
 
Posts: n/a
Default "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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default "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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Portuga
 
Posts: n/a
Default "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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default "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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM
How can I fix values in cells calculated by formula in Excel Leigh Excel Worksheet Functions 1 January 19th 06 07:36 PM
how to format only specific characters or numbers within each cellwithin a range of cells Colleen Excel Discussion (Misc queries) 4 September 12th 05 10:04 PM
Count cells with specific values in the cells next to them? Christopher Excel Worksheet Functions 2 September 8th 05 05:49 PM
Locate and delete specific cells David Smith Excel Discussion (Misc queries) 1 January 19th 05 04:45 PM


All times are GMT +1. The time now is 04:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement