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
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
Add a filter to the column, deselect the ones you don't want to erase. Select all the data and hit delete.
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.
PasteSpecialValues
onto column A ............to remove cells rather than clear them would require something a little more complex.
May 5, 2016 at 21:24