The following Taobao order record is exported from the system, which looks unconventional and neat, and is a more typical list of records.
But when you click on one of the cells, you find the problem in the formula bar, and there is a big space at the beginning of each cell:
The most direct impact of this situation is to interfere with our subsequent statistical summary, if you use a function formula to query the data, it will also be unable to find it because of the influence of spaces.
How do you get rid of these spaces? Let's take a look.
It is not possible to use Find and Replace, one is that these spaces cannot be displayed in the replacement dialog box, and the other is that if you really want to replace the spaces directly, the order number will become a scientific notation, even if it is set to text format first, you can try it.
The first method: segmentation
Click the column label letter of one of the columns to select the entire column, then click Data → then Column, select the Delimiter in the dialog box that appears, and then click Next.
In the pop-up Text Column Wizard (Step 2) dialog box, click Next to open the Text Column Wizard (Step 3) dialog box.
In the preview area, click the blank column label on the left and select Do not import this column.
Click the order column label on the right, set the column data format to Text, and click the Finish button.
As soon as the operation is done, the spaces in this column are cleared.
Next, you can draw the monk according to the gourd, and continue to work on the second and third columns......
The second method: PQ
If you are using Excel 2016 or later, you can click any cell in the data range, and then click Data → From Table/Region to load the data into the Power Query editor as prompted.
After loading into the Power Query editor, Excel will automatically convert textual numbers to numeric types by default, but in this way, the order number becomes a scientific notation, let's clear this automatic addition step first.
Hold down Ctrl, click the column labels for each column, and then click the Convert→ Format→ Clear commands.
Finally, click Home → Close & Upload to upload the data to Excel.
Then click the cell that had a space before, and look at it in the formula bar, the space is gone:
The third method: WPS forms
If you're using WPS forms, it's even simpler.
Select the data region, the WPS table will automatically display an error prompt button, click this small button, and select [Clear empty strings] in the shortcut menu:
Graphic production: Zhu Hongzhong