Home  >  Article  >  Topics  >  Practical Excel skills sharing: several practical operations of the "Column Sorting Tool"

Practical Excel skills sharing: several practical operations of the "Column Sorting Tool"

青灯夜游
青灯夜游forward
2022-05-12 10:18:385912browse

In the previous article "Practical Excel Tips Sharing: Implementing the Filtering Function after Merging Cells", we learned about the method of implementing the filtering function after merging cells. Today we talk about data sorting and share the practical operations of several sorting tools. I hope it will be helpful to everyone!

Practical Excel skills sharing: several practical operations of the

Suddenly my imagination opened up and I discovered that the column sorting tool is not just for dividing one column of data into two or three columns. It also has several other uses:

1. The data is divided into multiple columns

2. Quickly input date data

3. Extract a certain segment of data in the data

①The extracted data is displayed in Original position

②The extracted data is displayed in the designated column

The following will tell you the actual operation methods of these uses.

1. Data is divided into multiple columns

#In daily work, data entry is sometimes very casual, and the format is often inconsistent. For example, the name and model of the materials to be received are entered as "A-234", "A 234", etc. As shown in the figure below, in the daily material requisition registration form, product names and specifications are entered into cells in column F, and the format is not uniform.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

# During later data statistics, we need to sort the data in column F to separate the name and model. How to proceed?

Separating the name and model means adding a column of data, so you need to add an empty column first. Place the mouse on column number G. When the mouse turns into a downward arrow, click to select the data in column G.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Right-click in column G and click Insert.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

You can see that a column is inserted after column F.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Select the data area that needs to be divided into columns.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Click the "Column" button under the "Data" tab.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

In the pop-up dialog box, we can see that the data columns can be divided in two ways: "delimiter" and "fixed width".

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

The data in our table is separated by spaces and "-", so check "Delimiter" and click Next after setting it.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

In the new dialog box, we can see that there are many kinds of delimiters. We only need to check "space" here. The "Tab key" is checked by default, which indicates the position occupied by the characters. Just keep the default settings.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Since some of the data in the table above are separated by a single space, and some are separated by multiple spaces, you must check "Consecutive delimiters are treated as a single Process", you can see the data preview below, and the data separated by spaces has been separated.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

In the data preview, you can see that the data separated by "-" has not been divided into columns, so you need to check "Others" and enter "-" after it. ". Since some data are separated by multiple "-"s, you need to check "Consecutive delimiters are treated as a single process". At this point you can see that in the data preview below, all data is separated. After setting up, click Next.

1Practical Excel skills sharing: several practical operations of the Column Sorting Tool

#In the new dialog box, you can set and change the data format. We will explain this later. Just click Finish here.

1Practical Excel skills sharing: several practical operations of the Column Sorting Tool

#At this point you can see that the data has been divided into two columns, and the data in the second column is displayed in column G by default.

1Practical Excel skills sharing: several practical operations of the Column Sorting Tool

2. Quickly enter date data

When we enter date data in the form, most people manually enter "-" or "/" to separate the year, month and day data, which makes it very slow to enter numbers and symbols at the same time.

You must have never imagined that the fastest way to enter a date is to use data separation!

As shown in the figure below, we first directly enter the 8-digit number of the year, month and day. (Note: 8 digits refer to 4 digits for the year, 2 digits for the month, and 2 digits for the day. For example, February should be entered as 02, and 5th should be entered as 05.)

1Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Select the date area. Click the "Sort to Columns" button under the "Home" tab, click Next twice in the pop-up dialog box, and the dialog box shown below will pop up.

1Practical Excel skills sharing: several practical operations of the Column Sorting Tool

In the dialog box, our class can see that there are multiple formats to choose from. Here we only need to check "Date" in the dialog box and select the "YMD" format in the drop-down menu, which is the year, month and day format commonly used in Chinese. If you want to use the English mode, you can select the "MDY" mode, which is the month, day, and year mode.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

After setting, click Finish to see the final result.

1Practical Excel skills sharing: several practical operations of the Column Sorting Tool

3. Extract a certain section of the data

① Extract the data and display it in the original Bit

In previous operations, when we needed to extract the required characters from a long string of characters, we would use functions. Here we will teach you how to use columns to achieve it. As shown in the figure below, it is the data exported by the Human Resources Department in the attendance system.

1Practical Excel skills sharing: several practical operations of the Column Sorting Tool

We only want to keep the dates in the data, select the data area, and click the "Column" button under the "Home" tab, because the data here are all aligned Yes, we can directly check "Fixed Width" to divide. Of course, if you want to use "delimiter", you can also use it. I have already mentioned it before, so I won't go into details here. After setting up, click Next.

1Practical Excel skills sharing: several practical operations of the Column Sorting Tool

In the new dialog box, drag the mouse directly in the blank area of ​​the data preview below, and a dividing line will appear. Drag as many dividing lines as you need. Just place the dividing line where you need to separate. After setting up, click Next.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

In the new dialog box, select the data you do not need later.

2Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Check "Do not import this column" above.

2Practical Excel skills sharing: several practical operations of the Column Sorting Tool

After setting, click Finish to see the results.

2Practical Excel skills sharing: several practical operations of the Column Sorting Tool

At this point we can see that the extracted date is displayed directly in its original position, and the original data has been replaced.

② Extract the data and display it in the specified column

In our daily work, the most commonly used character extraction should be to extract the date of birth from the ID number. Bar. Below is the entered ID number.

2Practical Excel skills sharing: several practical operations of the Column Sorting Tool

In the previous operation, the date extracted from the attendance system was directly displayed in the original cell, and the original data was replaced. Here we need to extract the year, month and day, retain the original data, and display the extracted data in column H. The first few steps here are the same as above. Select the data, click "Column", select "Fixed Width", and pull out two dividing lines. After setting up, click Next.

2Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Select the first part of the data and check "Do not import this column".

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Select the last part of the data and check "Do not import this column".

2Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Select the data in the middle and click the arrow to the right of "Target Area".

2Practical Excel skills sharing: several practical operations of the Column Sorting Tool

At this time, the column wizard automatically shrinks back, and the H2-H11 area is selected in the table.

2Practical Excel skills sharing: several practical operations of the Column Sorting Tool

After setting the area, click the arrow on the right side of the column wizard.

Practical Excel skills sharing: several practical operations of the Column Sorting Tool

#The column wizard will automatically stretch out, select the "Column Data Format" as date "YMD", and click Finish.

3Practical Excel skills sharing: several practical operations of the Column Sorting Tool

The final results we see are as follows:

3Practical Excel skills sharing: several practical operations of the Column Sorting Tool

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: several practical operations of the "Column Sorting Tool". For more information, please follow other related articles on the PHP Chinese website!

Statement:
This article is reproduced at:itblw.com. If there is any infringement, please contact admin@php.cn delete