Excel table full contact to easily remove spaces in the table

Due to work needs, I often use Excel to process the data tables reported by various departments. Because the names of people have different words, there are always a lot of extras in the names in these tables. The space, and the position of the space is uncertain, the number of spaces is also uncertain, resulting in a very irregular table, and one adjustment is very troublesome. Is there any way to quickly remove these spaces? Today Xiaobian will share Excel with everyone. The problem of a large number of spaces in the form and the solution.

By the end of the year, the number of forms to be submitted by the unit is very large. Sometimes some non-professional teachers are invited to help, but in the process of entering, I found many teachers. There are some spaces in the entered form data, 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, which 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 range below the first name and click on the “Data” Validity & rdquo; command, pop-up "Data Validity" dialog box, in the "Settings" tab, the allowed validity condition is set to “Customize”, underneath, "Formula" Enter the formula “=(LEN(B2), LEN(SUBSTITUTE((B2)," ",""))))=0”.

Figure 1

Switch to the "Error warning" tab, type "& error" in the "title" box" error warning! & rdquo; in the "error message" box Enter "<; Enter the text with spaces, please re-enter after confirming!" (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, and if so, 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 the formula to remove the spaces in the data

The above tips will find that the system can automatically detect the presence of spaces, but can not automatically replace these spaces, you must also re-enter or Make the necessary changes after the input is complete. 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 enter It affects the user's line of sight. Later I applied the formula to sheet2 to solve this problem smoothly. Previous12Next page Total 2 pages