When editing an Excel table, won't blank rows be inserted based on the number of shoe sizes? Today, the editor will talk about the detailed steps of inserting blank rows according to the number of shoe sizes in the Excel table. Friends who need it can take a look together.
Step one:
1. In order to distinguish shoes with the same item number and different shoe sizes by separate lines, insert a blank line. Then the number of blank lines inserted is determined by the number of shoe sizes. In order to make it easier for readers to understand, the editor writes a title "Number of shoe sizes" in I1, writes the formula =LEN(C2)-LEN(SUBSTITUTE(C2,".",""))+1 in I2, and right-clicks The small black cross in the lower right corner of the cell, or filling the formula down, to the last row.
Explanation: This formula means the total number of characters, minus the number of text after replacing "." with space, to get the number of shoe sizes. C2 is the shoe size (original table title "Size") LEN returns the number of characters in the text string. The function of SUBSTITUTE is to replace old_text (old text) with new_text (new text) in the text string.
2. Use the auxiliary column: Enter the title "auxiliary column" in J1. Enter 1 in J2 and the formula =J2+I2 in J3. Then right-click the small black cross in the lower right corner of the cell, or fill in the formula downward to the last row with data (item number column).
Note: J2 is the number of shoe sizes corresponding to the item number calculated in the above steps.
3. Fill in the sequence number to establish the standard for inserting blank rows after sorting in the next step. In the last empty row directly below the auxiliary column of the table, enter the number 1, and then fill in the serial number downwards. The number of filled rows should be slightly larger than the value calculated in the bottom cell of column J (auxiliary column).
(For those who haven’t seen the previous post, let me explain again: Currently, shoe sizes with the same item number are all in one row, and our goal in redesigning the table is to divide them into separate rows, and fill in the same item number in adjacent rows. So insert a blank line.)
Step 2:
1. Delete duplicates: Copy and paste the data calculated by the above formula as a numerical value. Select columns A to J, click Data/Remove Duplicates, and deselect all. Then check "Auxiliary Column" in "Columns" under the "Delete Duplicates" dialog box and OK.
2. Sort: Select columns A to J and click Data/Sort in sequence (if you did not move or click the mouse, you should still select columns A:J in the previous step). In the Sort dialog box, in the Primary Keyword option, select Auxiliary Column. Sure.
3. After sorting, the effect of inserting blank rows according to the "number of shoe sizes" is achieved. If you use your eyesight to judge how many shoe sizes there are, and then manually insert rows based on the number of shoe sizes, in a table with thousands of rows, you will have to insert the year and month!
Friends who don't know the detailed steps of inserting blank rows according to the number of shoe sizes in Excel tables, don't miss this article brought by the editor.
