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.

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

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

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”

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

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

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

Previous

Next

Comments

Don Miller
June 19, 2015 at 9:45 pm

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.

Reply ↓

Leave a Reply Cancel reply

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.