Three methods to remove excess spaces in Excel

This article brings you relevant knowledge about excel, which mainly introduces related issues about clearing redundant spaces, including splitting, pq and table methods to clear redundant tables, as follows Let's take a look, I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

Today we share a problem that a friend encountered in actual work.

First take a look at the Taobao order record below. Are there any problems?

These records are exported from the system and look very regular, just a typical list of records. But when you click one of the cells, you will find a problem in the edit bar. There is a big space at the beginning of each cell:

This kind of The most direct impact of this situation is to interfere with our subsequent statistical summary. If you use function formulas to query data, you will not be able to find it due to the influence of spaces.

How to clear these spaces? Let’s take a look together next.

It is not possible to use search and replace. First, these spaces cannot be displayed in the replacement dialog box. Second, if you really want to replace the spaces directly, the order number will become scientific notation, even if it is set to The text format doesn’t work either. If you don’t believe me, you can give it a try.

First method: Column

Click the column label letter of one column to select the entire column, then click [Data] → [Column] in the pop-up dialog box Select [Delimiter] and click [Next].

In the pop-up [Text to Columns Wizard (Step 2)] dialog box, click the [Next] button directly to open the [Text to Columns Wizard (Step 3) Step)] dialog box.

At this point, you will see that the data is automatically divided into two columns. Click the blank column label on the left in the preview area, and then 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.

#With one operation, the spaces in this column will be cleared.

Next, you can draw a monk according to the gourd, and continue to process the second and third columns...

If there are 820 columns in the worksheet, then it will basically be processed until dark.

Second method: PQ

If you are using Excel 2016 or later, you can click any cell in the data area, and then click [Data] → [Auto Table/Range] and load the data into the Power Query editor according to the prompts.

After loading into the Power Query editor, Excel will automatically convert text numbers into numerical types by default, but in this way, the order number becomes scientific notation. Okay, let’s clear this automatically added step first.

Hold down Ctrl, click the column labels of each column, and then click the [Convert] → [Format] → [Clear] command.

Finally click [Home] → [Close and Upload] to upload the data to Excel.

Click the cell that had spaces before, and look in the edit bar, the spaces are gone:

# #Third method: WPS form

If you are using WPS form, the operation is simple.

Select the data area, the WPS table will automatically display an error prompt button, click this small button, select [Clear empty strings before and after] in the shortcut menu, and you’re done:

Related learning recommendations:

excel tutorial

The above is the detailed content of Three methods to remove excess spaces in Excel. For more information, please follow other related articles on the PHP Chinese website!