Due to the need of work, I often use Excel to process the data forms reported by various departments. Because the names of people have different words, there are always a lot of extra spaces in the names in these forms, and the position of the spaces is uncertain. Not sure, the form is very irregular, and one adjustment is very troublesome. Is there any way to quickly remove these spaces? Today Xiaobian will share with you the large number of spaces in the Excel table and the solution.
By the end of the year, there are a lot of forms to be submitted by the unit. Sometimes, some non-professional teachers are invited to help them. However, during the process of entering, I found that there are some spaces in the form data entered by the teachers, which affects the overall effect of the data. If it is time-consuming and laborious to modify one by one, I have come up with several effective solutions to this phenomenon and have received good results, so write them down and share them with the readers.
First, pre-embedded settings, do more with less effort
1. Remind users whether the data entered contains spaces
In the data entry process, if you want the system to automatically remind the user whether the data entered contains spaces Can be achieved by setting the validity of the data.
To verify whether the name data contains spaces in the input process (as shown in Figure 1), select the cell area with the first name down, and click “ Validity> in the “Data” menu ; command, pop-up "Data Validity" dialog box, in the "Settings" tab, set the allowed validity condition to “Customize”, enter in the "Formula" box below it. The formula “=(LEN(B2)-LEN(SUBSTITUTE((B2)," " ","")))=0”.
Figure 1
Switch to the "error warning" tab, enter “error warning!> in the "Title" box, enter “ in the "error information” box" The entered text contains spaces, please re-enter after confirming! & quoquo; (Figure 2), after the setting is completed, click the "OK" button to exit.
Figure 2
Next, in the process of entering the name, the system will automatically verify whether the current cell contains spaces, if any, the warning message shown in Figure 3 will pop up.
Figure 3
cico Tip: When Figure 3 appears, press Enter to re-enter the name. If you input more data with a larger number of words (similar to the “home address” field), it is not suitable to press the Enter key directly, otherwise the original data will be deleted automatically. Click the “Yes” button. The existence of spaces can be ignored.
2. Use formula to remove the space contained in the data
The above tips will find that the system can automatically detect the existence of spaces, but can not automatically replace these spaces, you must also re-enter or input to complete the necessary modify. Can you automatically remove the spaces in the input process?
If you insert some empty columns in the current table, you can use the substitute function to achieve this function, but always feel unattractive, and affect the user when entering Sight. Later I applied the formula to sheet2 to solve this problem smoothly.