When working with data in Excel, you often have to clear data from unnecessary rows and columns. This is easy to do when the rows that fall under the pattern to be deleted are few.
But, if you have tens or hundreds of thousands of rows, and you have several patterns, for example, the task may become impossible to complete.
What are patterns? These are the conditions on which you select the rows or columns you believe are unnecessary.
Examples of such patterns:
- a column or a row is empty,
- the presence of empty cells in the column,
- the presence of a particular character or several characters,
- the presence of several combinations of letters,
- the presence of several words,
- the presence of several word combinations.
For those who value their time and are looking for methods of fast problem solving, the tools for removing rows and columns in !SEMTools have been created. All macros in this menu work in the free version, so feel free to go to Download !SEMTools, install the add-in and use – they are quite handy. Here are the features in the section:
- Delete completely empty columns and rows on the sheet:
- empty columns
- empty rows
- Delete the entire row by condition
- Absence of anything (if the cell of the highlighted column does not contain):
- nothing (blank),
- letters or digits,
- Latin letters,
- digits,
- specific word,
- none of the words on the list.
- Presence of something (if the cell of the highlighted column contains):
- Latin letters,
- digits,
- specific word,
- a word made of digits (a number),
- any word from a given list.
- Cell content length: less or more than a given number of characters.
- Absence of anything (if the cell of the highlighted column does not contain):
See also: deleting rows and columns
- How to delete cells by condition in excel;
- How to remove certain words in Excel;
- How to remove specific characters in Excel;
- How to remove formulas from cells and leave only values.