Hi, Guys we know very well about the importance of MS Excel. If you have better knowledge about the MS Excel then you will be able to easily prepare MIS report. You can also get required data from large database after applying any condition. Today we have to discuss how to remove spaces with data validation in MS Excel. You know very well that Data Validation is an important feature of MS Excel.
Data Validation in MS Excel:
Data Validation is used to restrict invalid data to being entered in a particular cell or range of cells. There are lot of situation where you can use Data Validation as per your need.
TRIM Function in MS Excel:
TRIM Function is an important Text function of MS Excel. TRIM function is used to remove all leading and trailing spaces from any text string in MS Excel.
Must Read: VLookup Function in MS Excel with Appropriate Match
How to remove spaces with Data Validation in MS Excel:
If you want to restrict any kind of spaces during feeding any text string in the required cell then you have to use Data Validation along with Trim function in MS Excel.
You can prevent users from adding any kind of spaces before or after the text string or text entry with the help of Data Validation command along with TRIM Function. The TRIM function is used to remove spaces with Data Validation in MS Excel before and after the text, and any extra spaces within the text string.
E.g. If anyone want to known how could you prevent or restrict any user to insert any kind of spaces during typing the text entry in any cell. In that situation most of the persons not able to give the proper answer. Today we have to discuss how to use Data Validation with TRIM Function in MS Excel, to prevent any user to insert spaces within the text string. You must have to take following given steps how to remove spaces with Data Validation in MS Excel along with TRIM Function.
Steps how to remove spaces with Data Validation in MS Excel:
Step 1: Open or create a new sheet in which you want to remove leading and trialling spaces from excel.
Step 2: Select any cell for e.g. B2 in which you want to apply data validation rule > Now, Click on the Data Tab > Click on the Data Validation command > Click on the Data Validation option or, you can also press ALT+D+L short-cut key to directly open the Data Validation dialogue box.
Must Read:Excel: Find Duplicate Values with VLookup in different sheet
Step 3: In the Data Validation dialogue box Click on the Settings tab, select Custom option in Allow validation criteria then type given formula in the box: =B2=TRIM(B2) then press OK button to close the Data Validation dialogue box.
Step 4: Now take the cursor on cell B2 and type any text value. If you are trying to feed and text value with any spaces then MS Excel generate an error message on your computer screen due to the restriction rule.
Note: If you want to remove Data Validation rule from one or more cell from active sheet then first you have to select one or more than one cells > open the Data Validation dialogue box either from press ALT+D+L or click on the Data Validation command from the Data tab > Click on the Clear All button > Click on the OK button.
I hope you like this article and now you will be able to easily remove spaces with Data Validation in MS Excel. Kindly give your important suggestions in the comment box and also share your knowledge with your friends, colleague, family members and others. Thanks to all.