Data Validation in Excel

Original Question:-

How to stop user from entering space before or after string?

I do not want user to make any mistake i.e. there should be no space before or after any string. Formula should let user update with error message.

We can use either TRIM or combination of NOT, OR, LEFT & RIGHT functions in custom data validation in Excel.

We need to follow the below steps:

  • Select the range
  • Press ALT + D + L shortcut keys
  • From Settings tab click on Allow drop down & select Custom
  • In Formula box use =A1=TRIM(A1)

Custom Data Validation

 

  • The other formula is =NOT(OR(LEFT(A1,1)=” “, RIGHT(A1,1)=” “))

Custom Data Validation 2

 

  • Both the formulas will work & will not allow any space before or after the text.

In this way we can stop user from entering space before or after string in data validation.

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>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube