How To Prevent From Entering Space Before or After Text Using Data Validation In Microsoft Excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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.