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.