One of the most frustrating things that can happen when importing data is having multiple data types within the same column. For example I recently had a price file sent to me that had hardcoded cost prices e.g. $4.99, mixed in with discounts off the trade e.g. 30%. The data looked something like this:
Even though this is an extremely inconvenient data format, there are ways of separating the percentage values from the dollar values (without searching for the $ or %).
The first step is to create a new column next to the “problem” column. This will act as your filter column. After you have a space for your new filter column you will need to create a formula that will distinguish %’s from $’s.
In our case the output will be “True” for percentages and “False” for everything else. In the below example our problem data is in column “A”, if your data is somewhere else on the spreadsheet this will need to be changed accordingly.
In the first cell type:
This will grab the format of the data for that particular cell.
On percentage cells this will return “Px..”, where x is the amount of visible decimal places. E.g. 80.234% will return P3.
We only need to check the first character on the left to work out if the cell is a percentage, so change the formula to this:
The data will eventually be filtered, so there are only two values required for the output.
If the cell is a percentage output true, if it isn’t output false. Drag this formula down the column next to the problem data and it should output true for % values and false for anything else.
After you have your true false column with each cell denoting the status of the cell next to it the data can be filtered.
Highlight your new formula column
Click the Data tab
Change the newly created filter on the column to true
You will now have only the % values visible if you need the filter value should be false.
The whole process can be see here: