To prevent user from entering space before text, you can make use of Data Validation feature in Microsoft Excel.
This will be helpful for users while entering information if they have accidentally entered space before or after the text then the error message will prevent them from doing so.
With the use of Data Validation feature in Excel, you can create your own custom error message when the user enters the information which is not predefined.
In this example, we will use TRIM function.
TRIM: Removes all spaces from a text string except for single spaces between words.
Let us take an example:
Select the cell in which you want Data Validation to be applied.
Click on Data tab
Under Data Tools group, select Data Validation
Or use ALT + D + L shortcut keys for Data Validation
In Settings group, click Allow drop down
Select Custom, and in Formula box, enter the formula as =B4=TRIM(B4)
Select Error Alert tab & enter the Title & Error Message as shown in below screenshot
Now, when you enter space before or after the text, then the following dialog box will appear:
In case you want user to be able to enter the value skipping the error message
In Error Alert tab; select Style as Warning rather than Stop
After entering the space before or after the text; the following Warning dialog box will appear
If you want to continue, you can click on Yes button to accept your input value.
If you do not want to continue with the input value then you can click on No
In this way, you can create custom error messages that you want to show.
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.