How to extract the data you want in Excel

It's not that difficult is your question, it's just a lot of trouble.

Luckily, I'm a little patient, otherwise I'm afraid no one would want to do this job.

b1：mid(substitute(a1,"/","+",2),find(" ",substitute(a1,"/","+",2))+1,find("+",substitute(a1,"/","+",2))-find(" ",substitute(a1,"/","+",2))-1)

c1:mid(substitute(a1,"/","+",2),find("+",substitute(a1,"/","+",2))+1,find("*",substitute(a1,"/","*",3))-find("+",substitute(a1,"/","+",2))-1)

d1：mid(substitute(a1,"/","+",3),find("+",substitute(a1,"/","+",3))+1,find("*",substitute(a1,"/","*",4))-find("+",substitute(a1,"/","+",3))-1)

e1：mid(substitute(a1,"...","+",2),find("+",substitute(a1,"..."+",2))+1,find("*",substitute(a1,";","*",2))-find("+",substitute(a1,"...","+",2))-3)

Then pull the formulas down separately.

Excel how to extract the desired data from a **, excel is the office software we use most often in our work. We like to store a lot of data in Excel, but sometimes we only need to extract a part of the data, so how does Excel extract the data you want from a**?

After the installation is completed, we double-click on the icon to open it, and click "New" on the left side of it

Then select the icon and click "Create New Blank" below

How to extract the data you want in an excel sheet

1. Formula extraction method

In Excel, there are 3 commonly used functions for extracting data, left, right, and mid functions, and the function parsing and syntax formats are as follows

Second, the rapid filling method

It is a new feature in Excel 2013 and above. When using Quick Fill, first directly enter one to three sets of data, let Excel automatically recognize your intent, and then quickly fill. With the Quick Fill function, you can not only extract irregularities within cells.

Name, you can also extract numbers, English, etc. There are 3 ways to fill quickly:

1. Click the Data tab > in the Data Tools group> click Quick Fill;

2. Use the shortcut key Ctrl+E;

3. Select the sample cell, drag the fill handle to fill it down, and then select "Quick Fill" in the "Autofill" option;

Case Study:3. Use the custom function myget

The function has two arguments, =myget( where to extract, what to extract).

myget(string,0) takes out the number.

myget(string,1) takes out Chinese characters.

myget(string,2) to take out the English letters.

myget(string,3) to take out the special characters.

myget(string, 4) takes out the position of the first number.

myget(string, 5) takes out the position of the last number.

If you only need to extract numbers, you only need to set the second parameter to 0.

Method steps: First import the template file of the custom function, click the development tool vba or press alt+f11 to bring up the vba machine Right-click in the project window and select [Import File] Select the VBA module file (extract Chinese, English and numeric custom functions to close the VBA machine.) Then enter the formula.

How to extract the part you need from excel

1. Column A is full of four-character idioms, and the middle two digits are extracted from it.

2. Enter the formula for MID function guidance = mid(A2,2,2).

3. Press the Enter key to end the formula input.

4. Fill in the formula below, that is, the data is extracted, as shown in the figure below.

5. Rapid filling method:

Now enter the data you want to extract in the first cell.

6. Then click "Quick Fill" in "Data".

7. In this way, part of the data extraction is completed.

Click the "Advanced filtering" icon. Click "Copy filter results to another location". Select "List Area" and click on a cell range.

Select "Conditional Region" and click another cell range. Select "Copy to", click any cell, and then click "OK". Excel has looked up the same data and extracted it, detailed steps:

1. Open the computer**, and click the "Advanced Filtering" icon in the "Data" interface.

2. In the Advanced Filtering window, click "Copy Filtering Results to Other Locations".

3. Select "List Area" and click a cell range.

4. Select "Condition Region" and click another cell area.

5. Select "Copy to", click any cell, and then click "OK".

6. Excel has found the same data and extracted it.