Excel/Excel -- How do delete specific values/rows
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!
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
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:
( "_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.