Teach you how to handle spaces in Excel data tables

At the end of the year, the Unit to submit a very large number of forms, and sometimes asked some non-professional teachers to help input, but in the process of entry I found that many teachers entered the form of data in some spaces, affecting the overall effect of the data. If the modification is time-consuming and laborious, I have come up with several effective solutions to this phenomenon and have received good results, so write it down with the vast number of readers to share.

First, the early embedded settings, so that more than twice

1. Remind the user that the data entered contains spaces

In the process of data entry, if you want to let the system automatically remind users to enter the data contains spaces, you can set the validity of the data to achieve.

To verify that the name data contains spaces in the input process (Figure 1), select the range of cells with the first name down, click Effectivity on the Data menu, pop up the Data Validation dialog box, and set the allowed effectivity criteria to custom on the Settings tab, and under the formula box, enter the formula "= (LEN (B2)-len (Substitute (B2)," ",")) =0.

Figure 1

Switch to the Error Warning tab, enter the "Error warning!" in the Title box, and enter the text containing spaces in the error message box, please confirm and re-enter! (Figure 2), when the settings are complete, click OK to exit.

Figure 2

Next, in the process of entering a name, the system automatically verifies that the current cell contains spaces, and if so, pops up the warning message as shown in Figure 3.

Figure 3

Cico tip: When Figure 3 appears, press ENTER directly to re-enter your name. If you enter more words (similar to the "Home address" field), it is not appropriate to press ENTER directly, otherwise the original data will automatically be deleted, then click "Yes" button to ignore the existence of space.

2. Use the formula to remove the space contained in the data

The above techniques you will find that the system can automatically detect the existence of a space, but can not replace the blank space, but also have to re-enter or enter the completion of the necessary changes. Is it possible to automatically remove the spaces that are included 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 less beautiful, and in the input to affect the user's view. Later, I applied the formula to the Sheet2 to solve the problem successfully.

Before entering data, first switch to worksheet Sheet2 (renamed "Backup"), enter the formula "=substitute (SHEET1!A1," "," ")" in cell A1, and copy the formula to a range of cells as large as the table in Sheet1. When you enter data in the Sheet1, the data in the corresponding cell in the Sheet2 is automatically saved after the space is removed, and the data in the final Sheet2 is the data that we eventually need (Figure 4 and Figure 5).

Figure 4

Figure 5

Second, the late data processing, the same ease

Here we use the substitution method to delete the blanks in the table data.

Select the entire table, click Replace on the Edit menu, open the Replace dialog box, enter a space in the Find what box, and then click the Replace All button if you want to do nothing with the Replace Value box.

Cico tip: To make this substitution for a field, you can replace it by selecting the field before replacing it.

Of course, interested friends can also write VBA code to clear the space in the table data, but for the average user, the method described in this article is sufficient.

The method introduced in this article can guarantee that the data obtained is the result of our final need, even if it is not familiar with the Excel operation of friends can help input data, thus greatly improve our work efficiency.

        Note : more Wonderful tutorials please focus on the Triple computer Tutorials section, triple Computer office group: 189034526 Welcome to join