Prevent Spaces from Being Entered in Cells in Excel

Add to Favorites
Author: | Edits: don

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.
    96be5917f10c8ee328325e384e81df17.png

    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:
    10cbbc284983e707f05a9a30c6faf871.png
  3. Go to the Data tab and click Data Validation (or just hit Alt + D + L)
    7606d6b923e73e41a070f37a44f36d8e.jpg
  4. A window will open.  Click the Allow drop down menu and select Custom:
    18b55fc79f1c45f0cd996317abd4a3ba.png
  5. Where it says Formula, copy the custom formula that we created above.
    ae3c16b15b787730aa793eb29c07c61c.png
  6. Hit OK and now you can test it.
    2206dc4a4b80e2c560159e2e6fe924c3.jpg
    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:
    76d0bff7952b12b33afaebb0ee3c554e.jpg

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.


Excel Function: SUBSTITUTE()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Center Titles Across Multiple Cells in Excel
Tutorial: How to center a title across multiple cells in Excel in order to make good looking titles...
Disable/Enable Buttons in UserForms
Tutorial: How to have a button in a UserForm disabled until the desired event occurs. For instance, ...
Prevent Errors From Appearing in Excel
Tutorial: How to prevent errors from appearing in formulas in Excel. This is especially helpful for...
Get User Submitted Data from a Prompt in Excel using VBA Macros
Tutorial: How to prompt a user for their input in Excel. There is a simple way to do this using VBA ...
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to ...
Tutorial Details
Excel Function: SUBSTITUTE()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course