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.