Leading spaces can cause major issues with automated reports and Excel lookups as values that might have been set previously no longer match what is in the data.
Imagine if you create a monthly report which has summary tables that lookup values from the dataset. Suddenly that fancy summary of spend by the department you have created will no longer be accurate as your lookup is trying to find the department “Sales” whereas in the data it is now called ” Sales” with some leading spaces – Excel will not match the name and hence return no results.
So how to remove this unwanted leading spaces?
The answer is with the TRIM function in Excel. The TRIM function takes the straight-forward format of:
This is where the text part refers to your data cell which contains leading spaces or could contain them if you are taking preventative measures.
When you do not have the ability or perhaps the time to get the source data fixed you can apply a fix to the data in Excel using the TRIM function. The TRIM function will remove all leading blanks from the cell and you can use this to create a “corrected” version of the data which you will use in your Excel lookups.
Explore extensive sets of Excel Formulae here, to speed up your spreadsheet work.