How can I stop user to enter space at the begin and end of the string in Excel?

excel

#1

Hi,

I want to restrict user to enter space at the begin and end of the string. I am aware that restriction of input can be done using data validation but there is no direct option available for this restriction. Please help me to perform this using excel.

Thx, Manuel


#2

@Manuel,

You can perform it using custom option of data validation. Follow below steps to perform this:

Step 1: Select the cell to apply validation (I am selecting cell G6) and open Data validation window and select custom from Allow section under setting tab

Step 2: Put formula as =AND(LEFT(G6,1)<>" “,RIGHT(G6,1)<>” ") and click on Ok. I have used functions left and right to extract one character from left and right of the string and checked for space.

Hope this helps!

Regards,
Sunil