Data Validity to Prevent Space before Text in Microsoft Excel

In excel if you every come across a scenario in which you do not want the other user from entering space before text then this article is for you.


Question): I would like excel to pop up an alert message when the user enters space before text or numbers accidentally.


We need to follow the below steps:

  • Select the range where data validation needs to apply
  • Press ALT + D, L to launch data validation window.

image 1


  • From Settings tab click on Allow drop down & select Custom
  • Enter the formula as
  • =NOT(LEFT(A1,1)=" ")

image 2


  • Click on Input Message tab
  • In Title area enter “Error Alert”
  • In Input message box enter “Space before text is not allowed”

image 3


  • Click on Error Alert tab
  • Under Title enter your custom error as the title you want to display like “Input Message”
  • In Error message box enter the message as “Space not allowed in front of any cell”

image 4


  • Click on ok button
  • To test whether user is able to enter space before text or number, we will manually try to make mistake & let us see what happens

image 5


  • The best part of Input Message is when the user clicks on the cells in which data validation is already set then they will see the following input message

image 6


In this way we can use data validation to prevent space before text.

image 7




  1. Write some code for validity lists, that finds the current selection, then presents a new list with that the prior selection centered in the newly offered list, instead of the first or second half of a selection list. I use the validation selection method constantly looking for a dependent result in another cell.

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.