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



One thought on “Data Validity to Prevent Space before Text in Microsoft Excel

  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 *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>