You are here:

Excel/Excel -- How do delete specific values/rows

Advertisement


Question
Hi Tom,

I have been trying to figure this problem out, but I am not making progress.  The following is similar to my data:

Col A         Col B          Col C    Col D
1234567-H     Explanation1   2        02-02-2012
2323232       Explanation2   3        02-03-2012
9876543-UK    Explanation3   9        02-04-2012
134          Explanation4   8        02-05-2012
1234567       Explanation1   6        02-06-2012
521          Explanation6   7        02-07-2012
1234567       Explanation8   5        02-08-2012
1234567-199   Explanation9   3        02-09-2012
131          Explanation1   4        02-10-2012
1234567       Explanation0   2        02-11-2012

I want to delete all of the rows that only have three numbers in them, so the 134, 521, and 131 rows in the above example.

How can I do this?  Thank you!

Aska

Answer
Aska,

Your data is a bit jumbled, but I will assume that column A only holds the numbers or a number with a hyphen on the end.  I would insert a new column A to use as a "helper" column.  Assuming your data starts in row 1, then in A1 (after inserting the new column A) I would put in this formula

=if(len(trim(B1))=3,na(),"")

then drag fill this down column A as far down as you have data.  

this will put the #N/A error message next to any row that needs to be deleted.

Now select column A and hit F5 to get the GoTo dialog.  Click on the "Special" button in that dialog.  in the "special" dialog, choose Formulas, then uncheck everything but Errors:

__  Constants
_X  Formulas
  __ Numbers
  __ Text
  __ Logicals
  _X Errors

( "_X" represents the checkbox being checked,  "__" represents the checkbox not being checked)

then click OK

this will select all the cells containing  #N/A

Now go to the Home Tab and go to the "Delete" selection in the "Cells" command button on the Home tab.

click the down arrow and Choose Delete Sheet Rows

This should remove all the rows you don't want.  Now you can delete this new Column A to get back to just your data.

I use this method all the time and it only takes seconds once you get used to doing it.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.