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.

datavallist

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.

datavalidationnospaces01

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,” “,””)

datavalidationnospaces02

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.

________________

You may also like...

7 Responses

  1. David Tyler says:

    Would it be better to use =ISERR(FIND(” “,B3)) instead? The SUBSTITUTE method works fine for text, but it does not allow numbers. If someone were entering SKUs, for example, and she wanted to ensure no spaces in the SKU, she would need to have a more flexible data validation formula.

  2. Jason Morin says:

    David,

    I would just set the data entry cell as text if someone were to enter SKUs. Then the formula would work fine if someone were to enter numbers or letters.

  3. Devesh Shukla says:

    Is there any way that some can enter only numeric + characters both but not any special character.

    For Example:

    TEDSERVER01 –> Valid
    TEDSERVER$&01 –> Invalid

  4. Subhanakar Ganai says:

    Hello,

    This is very much help me .

    Thanks being helper.

    Subhankar Ganai

  5. km says:

    how to prevent a user from entering space in the beginning in a cell.

  6. Jim Palmer says:

    I want a user to enter a value from a list which includes January,February,March…

    The user has the habit of entering a space after a word.

    How can I ensure that they enter a value from the list and not include a space?

    In a macro I’ve had to use a workaround such as If Test =Trim(input) however it would be more convenient if the data validation rule worked.

Leave a Reply to Jason Morin Cancel reply

Your email address will not be published. Required fields are marked *