Prevent Spaces in Excel Data Entry Cell

From Contextures Blog

Prevent Spaces in Excel Data Entry Cell:
In Excel, you can use data validation to control (to some extent!) what users can enter in a cell. One option is to create a drop down list, so users can only select from a list of valid options.

Data Validation Custom Criteria

In some cases, a drop down list isn’t practical, but you still want some control over what goes into the cell. For example, in the screen shot below, we want to prevent users from typing a space character in the cell.



To do this, you can use data validation with custom criteria. Thanks to Jerry Latham for sending this example, and the custom validation formula.

Follow these steps to set up the data validation.

• Select cell B3, where the item code, with no spaces, will be entered.


• On the Excel Ribbon, click the Data tab, and click Data Validation


• In the Data Validation dialog box, on the Settings tab, select Custom


• In the formula box, type this formula, which refers to the active cell (B3), then click OK
  
  
  
  • =B3=SUBSTITUTE(B3," ","")

How It Works

The SUBSTITUTE function replaces each space character – " " – with an empty string – ""

The value entered in cell B3 must be equal to the result of that SUBSTITUTE function. If there is a space in B3, the results won’t be equal, so the data validation test will fail.

For more examples of Custom data validation, see the Data Validation Custom Criteria Examples page on the Contextures website.

________________