Teach you about the problem of excel data extraction a question that directs to a person .

=if(or(--right(int(abs(a1-b1)),1)=),0,if(or(--right(int(abs(a1-b1)),1)=),1,2))

If you want to equal other numbers, then you can fill in the number you want to equal to

Is it a file of hundreds of worksheets, if so, just take the problem as the content (the subject of the email must contain "excel", I use this as a basis to identify non-spam, so as not to delete by mistake), and the excel sample file (show the current situation and target effect, and explain how to get the target effect from the current situation) as an attachment to see it.

The mid function is a find function that starts from extracting the specified position and finds the location of the string with a specified length.

" can be represented as find(":", a1) can be represented as find(":", a1)-1, which has a length of 1

So the formula is:

mid(a1,find(":", a1)-1, 1) and then pull down.

Wait and see, I'll start with my thoughts, if your circled number is 9 digits, you can do it all at once.

You can use the formula:

sumproduct((Condition 1)*(Condition 2)*(Condition 3)*(Statistical Value Area)).

Wherein: Condition 1, Condition 2, (can be increased as needed, and the middle is operated with a multiplication sign *) is:

a1:a1000=2014/9/3

b1:b1000="c"

c1:c1000=123

The statistical value area is.

f1:f1000

This is an example, of course, where the data types need to match (you can also put the data you are looking for in a cell, so that you can change the data you are looking for at any time).

The same will be in excel.

How to extract each line of the copy content:

Tools Raw materials.

win7 system computer office2010

1. First open the Excel software, find the data that needs to be operated (here is A2:A10), and extract the duplicate data to a specific location (here is E2:E10).

2. Use the mouse to select all the contents of e2:e10.

3. Type the formula in the middle column: =if(countif($a$2:a2,a2)>1,a2,"")。

4. Press the "Ctrl+Enter" on the keyboard to get the result.

1.Open the list that needs to be processed, as shown in the figure, you can see that there are two parts of the same in the upper and lower data tables, in order to facilitate the explanation and final comparison, the data can be seen at a glance, but in the actual operation, the general data is more messy.

2.Add a secondary title block to the right of the original data list by copy-pasting, and the same part will be displayed there.

3.In the toolbar, find "Data" - filter "- Advanced".

5.Select another set of data in the Conditional Area.

6.In the dialog box, select Copy filter results to another location

7.In the "Copy to" column, select the secondary toolbar added in step 2.

8.Click OK to finish.

Use a filter and you're good to go.

Data Filtering Automatic filtering.

Then click on the small triangle of the address column to appear in the drop-down menu, and select Custom to appear in the dialog box.

Select "Include" from the drop-down menu on the left, and enter Beijing on the right.

You enter =vlookup($a 3,sheet1!) in b3 of sheet2$a:$e,column(),false) first fill the formula to the right, and then fill the formula downward.

Depending on the situation:

1. If the data is just dead, you can directly copy and paste it in another worksheet;

2. If the data is changing, or there is a formula, enter the equal sign "=" on the corresponding cell of the worksheet that needs to quote the data, then click on the worksheet of the referenced data, select the data cell and click, and press the enter key to complete the data extraction.

vlookup function.

vlookup(b3,sheet1!b: e,3,false) (if: Table 1 quotes the selling price from Table 2, taking the trade name as an example).

vlookup(b2,sheet2!b2:d5,3,false) (if: Table 2 uses the purchase price from Table 1, and the trade name is a keyword for example).

It can be done like this:

b3=vlookup($a3,sheet1!$a:$e,column(b3),)

Copy right down.

Function: It is to extract the first character from the left, take a few digits, =left(a1,11) that is, take the first 11 characters;

Function: start from the first digit, extract the digits, =mid(a1,1,4), that is, from the first digit, take 4 bits;

Function: It is to extract the number of digits from the right, that is, to extract the number of digits from the back to the front, and =right(a1,3) to take the last 3 digits.

There are many methods, depending on the specific data, it is a fixed first 11 characters, and it is the right choice to use the left function directly.

Solution:

1. If you want to modify the data in this column directly: then select the column - select "" - replace " - > search content on the excel menu and fill in "-g"; "Replace with" is not filled in - > "Replace all".

2. If you want to generate new data in other free columns (such as data in column A, column D in free column), enter =left(A1,11) in cell D1 and then pull down to copy.

If your data is as you listed then canonical (the same character length), you can use the formula to complete column f = left(e1,1).

Column g = mid(e1,8,1).

Column H = mid(E1,10,4).

Data is a column, with a separator of "-".

The steps to extract the first few digits in Excel are as follows:

1. Open the required excel**.

2. If you need to extract the first two digits on the left side of the numbers in column A, enter =left(A1,2) in column B1, "left" means that you need to extract numbers from the left, "A1" means the number column you want to extract, and "2" means the number of digits you want to extract, and then press Enter.

3. After success, you need to extract the number of column A, just click on the B1 grid, a solid black dot will appear in the lower right corner, left-click the black dot and drag it down.

4. If you need to extract the last 5 digits on the right side of the numbers in column A, enter =right(A1,5) in the B1 column, "right" means that you need to extract the numbers from the right, "A1" means the number column you want to extract, and "5" means the number of digits you want to extract, and then press Enter, click B1 and drag down.

5. If you need to extract the middle 2 digits of the numbers in column A starting from the 4th digit on the left, enter =MID (A1,4,2) in the B1 column, "MID" means that you need to extract the middle number, "A1" means that you want to extract the number column, "4" means that you need to extract the 2nd digit from the 4th digit, "2" means that you need to extract two digits, and then press Enter, click B1 and drag down.

6. If you want to extract the date of birth in the ID number, you can enter =mid(A2,7,4)& in the B2 column"years"&mid(a2,11,2）&"month"&mid(a2,13,2)&"day" ，&"years"That is, add the year after the data, and the rest is the same, just drop down the carriage enter.