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 | | | | |
  • 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 '16 at 16:48
  • Personally I would try to filter the data and then just delete what you don't need – Raystafarian May 5 '16 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 '16 at 17:54
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 | | | | |
  • This answer would be better if you could include a quick screenshot of the filter in action. – Burgi May 11 '16 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 | | | | |
  • How does this clear the data from the cell if it doesn't meet the criteria described by the OP? – CharlieRB May 5 '16 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 '16 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 '16 at 21:24
  • Sure with copy and paste special values the user can have the column clear as he wants it – user555689 May 5 '16 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 '16 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.