Forum Discussion
Please help- deleting rows containing a specific value from a large set of data
Hi, I'm doing a project for a class at my university and need to clean up some data. For this data, any missing data is put in as "-9999". I need to delete all rows that contain -9999, but can't figu...
Can you create a new column/field that searches the rest of the row for "-9999"? If so you could use that to filter it out. Then if sorting is permitted you could sort by that column, select all rows, and delete them.
- How would I make a column that searches the row for it?
- In the new column, first cell, start with one cell to search for the criteria. I don't know if you are expecting an exact match or searching for that as text within a string. For exact match it's just
=if(A2=-9999,"match","no match")
Otherwise you need to use the FIND function.
If you are looking in multiple columns for that text, I can only figure out how to use one column to test per column of data.
=IF(FIND("-9999",A2)>0,"found","not found")
In my formula if it's not found, then you get #VALUE, but you could use it.
