Let’s say we’ve created a form for user input, like this one:
When entering, there is always the possibility of incorrect information entry, the “human factor”. One of the options for its manifestation is extra spaces. Someone puts them randomly, someone intentionally, but, in any case, even one extra space will present a problem for you in the future when processing the entered information. An additional “charm” is that they are not yet visible, although, if you really want to, you can make them visible using a macro.
Of course, it is possible and necessary to “comb” the information after entering it with the help of special functions or macros. And you can highlight incorrectly entered data right in the process of filling out the form, promptly signaling an error to the user. For this:
- Highlight the input fields where you need to check for extra spaces (yellow cells in our example).
- Choose on The main command tab Conditional Formatting – Create Rule (Home – Conditional Formatting — Create Rule).
- Select rule type Use a formula to determine which cells to format (Use formula to determine which cells to format) and enter the following formula in the field:
where D4 is the address of the current cell (without “$” signs).
In the English version it will be, respectively =G4<>TRIM(G4)
Function TRIM (TRIM) removes extra spaces from the text. If the original content of the current cell is not equal to “combed” with the function TRIM, so there are extra spaces in the cell. Then the input field is filled with a color that can be selected by clicking on the button Framework (Format).
Now, when filling in extra spaces “for beauty”, our input fields will be highlighted in red, hinting to the user that he is wrong:
Here is such a simple but nice trick that I have used many times in my projects. I hope you find it useful too 🙂
- Cleaning text from extra spaces, non-printing characters, Latin characters, etc.
- Tools to remove extra spaces from the PLEX add-on
- Protect sheets, workbooks and files in Microsoft Excel