1

I have a column that has about 500k rows. I need to make every row BLANK that doesn't contain S, M, L, XL or XXL

I don't want to remove the row, just replace them with nothing

Improve this question
3
  • I haven't tried much i'm not that good with excel. I no how to find and replace but how can i keep only the few things i want and remove the rest.
    – user590724
    May 5, 2016 at 16:48
  • Personally I would try to filter the data and then just delete what you don't need
    – Raystafarian
    May 5, 2016 at 16:51
  • 1
    you want to clear the cell if it does not have one from the list, or clear the entire row, leaving a gap?
    – Yorik
    May 5, 2016 at 17:54

2 Answers 2

Reset to default
3

Add a filter to the column, deselect the ones you don't want to erase. Select all the data and hit delete.

Improve this answer
1
  • This answer would be better if you could include a quick screenshot of the filter in action.
    – Burgi
    May 11, 2016 at 11:06
2

You can use IF with Find, but notice that Find is case sensitive, so you have to write S, M, L, XL, XXL the same way in the Formula like in the cells, you can use the following but it is a nesting If, supposing your data is in column A in B write:
=IF(ISNUMBER(FIND("S",A1,1)),A1,IF(ISNUMBER(FIND("M",A1,1)),A1,IF(ISNUMBER(FIND("L",A1,1)),A1,IF(ISNUMBER(FIND("XL",A1,1)),A1,IF(ISNUMBER(FIND("XXL",A1,1)),A1,"")))))
And you can drag it down.

Improve this answer
6
  • How does this clear the data from the cell if it doesn't meet the criteria described by the OP?
    – CharlieRB
    May 5, 2016 at 20:10
  • @CharlieRB it makes Blank if the cell does not contain S, M, L, XL, XXL what else is the criteria???
    – user555689
    May 5, 2016 at 20:58
  • I think your approach is valid.....if the Poster wants to clear cells in the original data (that is column A); all they need to do is to copy column B and PasteSpecialValues onto column A ............to remove cells rather than clear them would require something a little more complex.
    – Gary's Student
    May 5, 2016 at 21:24
  • Sure with copy and paste special values the user can have the column clear as he wants it
    – user555689
    May 5, 2016 at 21:31
  • All I was looking for was you to explain that in your answer rather than leave it to the OP to figure out.
    – CharlieRB
    May 6, 2016 at 11:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .