The easiest way to validate data Data validation in Excel
How to prevent space before or after a string? I do not want the user to make a mistake and there is a gap before or after each string. I want to give an error message to the user in case of an error.
To do this in Excel we can use TRIM or a combination of NOT , OR , LEFT and RIGHT functions to customize the data validation.
Follow these steps:
- Select the range on which you want to validate.
- Press the ALT + D + L shortcut keys .
- In the window that opens, from the Settings tab , click on the Allow drop-down option and select Custom .
- In the box (Formula , = A1 = TRIM) write A1 .
- Another formula you can use
- = NOT (OR (LEFT (A1,1) = ”“, RIGHT (A1,1) = ”“))
- Both formulas work well and leave no space before or after the text.
In this way, we can do something in the data validation section to prevent the space before and after the text from entering.