If, like me, you like to keep your data clean when it is entered into your worksheet, this is something you need to learn. The good news is that this is also something that helps to prevent corruption in your Excel worksheets as well.
Follow the steps below to learn how to prevent spaces being entered in your worksheet:
- First, there is a custom formula that you will need for your data validation:
- =A1=SUBSTITUTE(A1,” “,””)
- I personally think you should create your formula first instead of within the Data Validation window because it will be easier to troubleshoot problems in your worksheet itself.
- You can simply change A1 (in your formula) to the first cell in the list that you create where you do not want to allow blanks.
- All the above formula does is 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 would like to see this function in action, just key in =SUBSTITUTE(iA1, ” “, “”) into a cell in your worksheet and then key in text that contains a space in cell A1.
- Select the range where you would like to prevent spaces from being entered.
- Make certain the first cell in the selection is the cell in the formula you created above (A1).
- Go to the Data tab and click Data Validation. I prefer to use the key combination shortcut of ALT + D + L.
- The dialog box will open.
- Select Custom from the drop-down menu.
- Where it says Formula, copy the custom formula that we created above.
- Click OK.
Now, spaces will not be allowed in the cell. Go ahead and test it!