Prevent Spaces from Being Entered in Cells in Excel

How to prevent a user from entering any spaces within text in Excel.  This allows you to keep data clean when a user is entering something in Excel and helps to prevent data corruption.

We will use data validation in Excel in order to achieve this result.

Steps to Prevent Spaces Being Entered in Text in a Range in Excel

 =A1=SUBSTITUTE(A1," ","")

1. Create the custom formula that is needed for the data validation.
   
   

   I always create the formula in the spreadsheet first instead of within the Data Validation window because it is easier to troubleshoot problems within the worksheet itself.

   Simply change A1 to the first cell in the list that you want to not be allowed to have blanks.

   All this formula does is to see if the original text in cell A1 is equal to the text in A1 when we replace any space characters with nothing; this replacement is achieved with the SUBSTITUTE() function (if you want to see this function in action, just type =SUBSTITUTE(A1," ","") into a cell and then put text that contains a space in cell A1.

2. Select the range where you want to prevent spaces from being entered; make sure the first cell in the selection is the cell in the formula you just created, for us this is cell A1:
   
3. Go to the Data tab and click Data Validation (or just hit Alt + D + L)
   
4. A window will open.  Click the Allow drop down menu and select Custom:
   
5. Where it says Formula, copy the custom formula that we created above.
   
6. Hit OK and now you can test it.
   
   We can add values with no spaces, but, regardless of where we are in the list we selected in step 2, we still can't add spaces:
   

Prevent Other Non-Space Characters from Being Entered into a Cell

To achieve this, simply replace " " in the formula we created in step one with anything you want: "a", "&", 1, etc.

 =A1=SUBSTITUTE(A1,"a","")

 =A1=SUBSTITUTE(A1,"&","")

 =A1=SUBSTITUTE(A1,1,"")

Notes

Make sure that when you enter the formula in step 1 above, you do NOT put dollar signs in front of the cell references; that would make the cell references absolute and they would not update appropriately when you apply the data validation to the entire range or list.

You may notice that some of our other Data Validation tutorials devote an entire section to building the custom formulas and that is because some of those formulas are rather complex; the formula for this tutorial is pretty simple though, so we didn't feel it needed much more explanation.

Make sure to download the accompanying Excel workbook so you can follow along with this tutorial.