Prevent Spaces in Excel Data Entry Cell

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.

Set Up Data Validation

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.
________________

11 thoughts on “Prevent Spaces in Excel Data Entry Cell”

  1. 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. 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. 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. 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.

  5. Debra, I was searching for a way to prevent users from entering spaces before and after entries and found this one of yours: http://www.contextures.com/xlDataVal07.html
    I tried several of the options (TRIM, SUBSTITUTE) but it only works with text, not numbers. So if I enter a number preceded or followed by a space, the data validation rules still allows it. However, if I format the range as text, it then will prohibit it. Why is that? Thanks,

    1. Michael, when you press Enter, Excel automatically strips out any leading or trailing spaces, if it sees the entry as a number.
      So, when the data validation rule checks the number, there aren’t any spaces, and the entry is valid.
      If you format the cell as Text, those spaces are NOT stripped out automatically, and the DV rule shows its error message.

  6. The quotes for the formula are confusing. It would be helpful to put the formulas in a code block or to use a different font that didn’t automatically add opening and closing quotes.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.