2 Easy Ways to find blank spaces in Excel

In Excel, it’s important to make sure that the data we work with is clean and doesn’t contain unnecessary spaces.

Blank spaces, especially trailing or leading spaces in cells, can cause issues in data processing and analysis.

In tasks like sorting, filtering, and applying formulas, hidden spaces can lead to incorrect results too.

If you need to identify cells that contain spaces, you can easily do so by highlighting the cell contents.

In this article, we’ll guide you through a simple process to achieve this.

Find Blank Spaces with Conditional Formatting

Step 1: Select the range of cells.

Start by selecting the cells you want to highlight. You can select a single cell or a range of cells. You can also select an entire column or row by clicking the corresponding column or row heading.

Step 2: Open the “Conditional Formatting” dialog box.

Once you’ve selected the cells, go to the “Home” tab on the ribbon and click on “Conditional Formatting” in the “Styles” section. From the drop-down menu, select “New Rule”.

Step 3: Choose the “Use a formula to determine which cells to format” option.

In the “New Formatting Rule” dialog box, select the option that says “Use a formula to determine which cells to format”.

Step 4: Enter the formula.

In the formula field, type the following formula:

=IF(ISNUMBER(SEARCH(" ",A1)),TRUE,FALSE)

Here’s a breakdown of how it works:

• SEARCH(” “, A1): This part of the formula searches for a space character (” “) within cell A1. The SEARCH function returns the position (as a number) of the first occurrence of the space character within the text. If no space is found, SEARCH returns an error.
• ISNUMBER(SEARCH(” “, A1)): This part checks if the result of SEARCH(” “, A1) is a number. ISNUMBER returns TRUE if SEARCH finds a space (since it returns a numerical position) and FALSE if SEARCH returns an error (meaning no space was found).
• IF(ISNUMBER(SEARCH(” “, A1)), TRUE, FALSE): This is the full IF function. It evaluates the condition ISNUMBER(SEARCH(” “, A1)). If this condition is TRUE (meaning a space is found), the IF function itself returns TRUE. If the condition is FALSE (no space is found), the IF function returns FALSE.

Note: Replace “A1” with the cell reference of the first cell in your selection. If you’re highlighting an entire column, it’s important to use a cell reference that’s in the same row as the first cell in the selected column.

Step 5: Choose the formatting.

Next, click on the “Format” button to choose the formatting you want to use. You can choose a different font color, fill color, or border style. When you’re done, click “OK”.

Step 6: Save the rule.

Finally, give your rule a name (optional) and click “OK” to save it.

Done! You’ve now successfully highlighted the cells that contain spaces.

Excel’s conditional formatting feature allows you to highlight cells containing spaces quickly and easily. By following the above step-by-step instructions, you can easily create a rule that highlights cells with spaces so that you can take necessary action to remove them.

Find Blank Spaces with Office scripts in Excel

Office Scripts in Excel is a powerful automation feature that enables users to record, write, and run scripts to automate repetitive tasks and streamline workflows in Excel.

The following script loops through cells in a selected range of an Excel worksheet and identifies cells containing spaces by changing their fill color.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let range = selectedSheet.getUsedRange();
    // Check if the range is not null
    if (range) {
        let values = range.getTexts();     
        // Loop through all rows and columns in the range
        for (let i = 0; i < values.length; i++) {
            for (let j = 0; j < values[i].length; j++) {
                let cellValue = values[i][j];
                // Check if the cell contains a space
                if (cellValue.includes(" ")) {
                    // Get cell
                    let cell = range.getCell(i, j);
                    // Do something with the cell that contains space
                    // For example, setting the fill color to yellow
                    cell.getFormat().getFill().setColor("yellow");
                }
            }
        }
    }
}

In this script:

• We get the active worksheet and its used range.
• Then, we loop through each cell in the range.
• We check if each cell’s text includes a space using the includes(” “) method.
• If a space is found, we do something to mark the cell – in this case, setting its fill color to yellow

To use this script, open your Excel workbook, navigate to the “Automate” tab in Excel for the web, and paste this code into a new script. Running the script will highlight cells containing spaces.

Conclusion

In conclusion, finding blank spaces in Excel is essential for maintaining accurate and reliable data. Using Conditional Formatting and Office Scripts makes this task easier and more efficient.

Conditional Formatting helps you quickly spot spaces visually, while Office Scripts automate the process for larger data sets. Both methods are great tools to keep your data clean and error-free, enhancing the quality of your work in Excel.