Why Your Google Sheets Formulas Are Breaking

May 26
1 min read

This is one for the formula writers, as this can be a formula killer. We are talking about whitespace, invisible spaces ruining your formula, sometimes before or after the text. This can happen a lot if data has been imported from somewhere else, and can lead to you having no idea why your formula isn't working.

They are easy to miss at a glance (or even the tenth stare), and here is how to quickly fix it.

Step 1 - Highlight the data (the entire column or range) that you want to clean up.

Step 2 - On the top menu bar, click on the Data menu.

Step 3 - Hover your mouse and click Data cleanup.

Step 4 - Select Trim whitespace.

Spreadsheet with names in blue cells; dropdown menu open showing options like Sort, Filter, and Data clean-up with Trim whitespace highlighted.

A pop-up will appear and tell you how many cells had whitespace trimmed! It would be good to do this as part of your process when importing data, to remove the impact on your VLOOKUPs.