After importing raw data in Excel from an online data source or after the conversion from PDF to Excel format, which is done with our service, the next step would be to clean up your data. Before we start analyzing our data, we need to clean it from unnecessary characters, unknown symbols and everything that might interfere in conducting proper data analysis. This is a necessary step because if you are copying or importing data from various sources into Excel, it is possible that some invisible characters or spaces are accidentally created. These characters will cause a problem if we want to calculate something or apply a formula to the document.
Fortunately, Excel has some great functions that we can use to fix this and today, we are going to show you how to use them. If you know of another good solution, please share it with us in comments.
1) Trim function
The Trim function clears unnecessary spaces in cells.
For example, if there is space before data in a cell, it can make searching merging or calculating a bit difficult. This is because, if it is a text field, then the distance is calculated as the portion of the cell.
2) Clean function
The Clean function enables us to remove some problematic nonprintable characters, like the ones that are invisible and cause errors or mistakes. Such characters are often created when importing data from other sources.
3) Substitute function
SUBSTITUTE function allows you to replace one type of characters with another.
For example, as shown in the image, you can replace spaces with blank fields. Keep in mind that in some versions of Excel you have to type in a comma between the values, and in other versions, you have to type in semicolons.
4) Find and replace tool
Use Find and Replace to manually replace the values in the cells.
Select the cells that you want to modify. Press the CTRL + H, a new menu will open. In the first field enter the character that you want changed, and in another field enter the character you want it changed into. This can be a space, or nothing (empty field). In our example, we deleted all spaces by replacing the space character with a blank field.
5) Data sorting
Sorting data through different criteria can be highly beneficial, because it allows us to move data around, delete, copy or view how the data looks.
You can choose multiple levels for sorting and also decide if the data should have headers.
