Ever wondered why your VLOOKUP or SUMIF functions don't work with downloaded data?
Often when you download data into Excel from other systems there are leading and trailing spaces added to the data. Leading spaces are easy to see in the cell, but trailing spaces are harder to identify.
To spot trailing spaces select a cell and press the function key F2. Check where the cursor is positioned in the formula bar to see if there are extra spaces added to the end of the data.
These extra spaces will cause issues with some functions which require exact matches. A single space in the data could stop the function from working.
Excel can remove these unwanted spaces with the TRIM function. This removes leading and trailing spaces, but doesn't affect any spaces within the data itself. If A1 had the data that need to be corrected the formula =TRIM(A1) would remove any leading and trailing spaces.
You could then use Paste Special > Values to paste the result of the TRIM function on top of the data to get it ready to use.