HOW DO I USE EXCEL FUNCTIONS TO EXTRACT THE RELEVANT FIELDS I NEED?

The field is also a rule, so you can use the find lookup keyword to determine the location, and then use the mid extraction.

mid(ad2,find("ID number",ad2)+5,18)

Sometimes we often need to extract characters from excel, but what if we are not familiar with them? Today, I will teach you the meaning and usage of the commonly used extraction field functions (left, right, mid, find, etc.).

Turn on step-by-step reading mode. How it works:

The first is the case where the first few fields have the same number of words, which we can use"left"Function.

For example, if we want to extract the first two words, we can enter them"=left(d4,2)", which means extracting the first two words in cell d4.

After pulling down, you can quickly extract the first two words.

The same goes for if used"right"The function can extract the fields with the same number of words in the future, so I won't repeat the list here.

If you want to extract fields with different numbers of words in the last few digits of a cell, you can use it"mid"Function.

For example, the function here"=mid(d4,4,4)"Indicates 4 characters after the 4th character of cell D4 is extracted.

After dropping down, all the fields after the 4th character are extracted.

However, if you need to extract the number of bits of the previous characters, it is not enough to use the mid function alone, and you need to add it here"find"Function.

Like here"find("with",d4)"Indicates the search for characters in cell d4"with"The number of characters in which it is located.

mid(d4,find("with",d4)+1,4)"It means extracting the characters in cell d4"with"The next 4 characters.

After the drop-down, the eligible characters are extracted.

The text function can be realized, with the find function, to determine the start and end position of the extraction, with the mid function, to extract the number of the specified number of digits to the macro, <>

The formula is: =left(a2,find(.";a2,1)-1) The second formula is: =mid(a2,find(";a2,1)+1,find("then accompany",substitute(a2,"Greetings; ",2),1)-find(";a2,1)-1)

If necessary, please continue to contact!

I'll also come to one, b2 cell input formula =iferror(mid($a 2,find("@substitute(";a2&";Bo Jingpei,"@column(a1)))find("@substitute(";a2&";column(b1)))1-find("@substitute(";Kiwei &$a 2&";Manuscript Wang,";column(a1)))

Example.

B2 formula = trim(mid(substitute(A2,";rept(" 100)),1+(1-1)*100,100))

1-1) The first 1 represents the difference between the first few points of the virtual carrying code and the hidden band number.

Material Tools: Excel2007

1. For example, in this excel**, we want to extract the employee's name.

2. First, select the right box (column) corresponding to the cell (column) that needs to extract information, for example, if you want to extract from column A, select B2 from A2.

3. Click the input box under the toolbar (on the right side of the word fx), and enter the formula as follows: =right(a2,len(a2)-find("Ministry",A2)),Click Enter,B2 becomes Li Li。

4. Select the B2 cell, move the mouse to the lower right corner of the cell, when the mouse pattern changes to a black solid cross, hold down the left button and pull down to the last column to be filled.

5. In this way, the field after extracting the specific characters in column A and displaying it in column B is completed.

6. If you want to extract the department information, click C2 to enter the formula for the first three words in column A.

7. The formula is as follows=left(A2,3), that is, take the left 3 characters in the A2 cell to display.

8. Click the enter key C2 to display the department information.

9. Move the mouse to the lower right corner of C2 and click the drop-down or double-click the left button to extract successfully.

=left() takes the value from the left, for example: =left(a1,3) The result is to extract the first three digits of a1.

right() takes the value from the right, for example: =right(a1,3) The result is to extract the last three digits of a1.

The number of digits from the digit of mid(), e.g. =mid(a1,3,3) is calculated to extract the three characters from the third digit of a1.

Let's say this cell is in b2

Select B2 and use the data --- column--- and the space as the condition to separate the fields.

Then cut and copy it into the cell you want.

Let's assume that the cell where the data is located is in A1

Take the value before the first space xx province.

left(a1,find(" ",a1))

Take the value between the first and second spaces xx city.

left(right(a1,len(a1)-find(" ",a1)),find(" ",right(a1,len(a1)-find(" ",a1)-1)))

Take the value between the second and third spaces xx county.

left(right(a1,len(right(a1,len(a1)-find(" ",a1)-1))-find(" ",right(a1,len(a1)-find(" ",a1)-1)))find(" ",right(a1,len(right(a1,len(a1)-find(" ",a1)-1))-find(" ",right(a1,len(a1)-find(" ",a1)-1)))

Extract the characters from left to right before the third space xx province xx city xx county.

left(a1,find(" ",a1,find(" ",a1,find(" ",a1)+1)+1)-1)

Take the characters in the middle of the first space to the third space: xx city, xx county.

left(right(a1,len(a1)-find(" ",a1)),find(" ",right(a1,len(a1)-find(" ",a1)),find(" ",right(a1,len(a1)-find(" ",a1)))1)-1)

The find function is used to find the position of a character in a string, for example: =find("City",A1) Find the first few characters of the city character in A1. For example:

find(" ",a1,4) from the 4th character to find the space ( in a1" "In the first few characters.

The len function calculates how many characters there are, e.g. =len(a1) counts how many digits there are in a1.

Extract the first three words in cell A1: left(A1,3).

Extract the last three words in cell A1: right(A1,3).

Extract the 3rd 5 words in cell A1: mid(A1,3,3).

=left(a1,find("#",substitute(a1," ","#

The thing before the second space is extracted as is.

One more is included.

left(a1,find(" ",a1,find(" ",a1)+1))

You can use formulas, and more simply, you can use the data sorting function.

Select Whole Column-Data-Column-Separator-Space.

When using excel to process data, we often need to extract the characters and fields in the cells, how does this work, let's take a look at how to extract characters and fields.

In Excel, you can use the left function to extract part of the text from the left side of a cell, the right function to extract part of the orange text from the right side, and the mid function to take part of the text from the middle.

Tools used: Office2007

Taking the middle part of the text as an example, the steps are as follows:

1.Extract provinces from column A data:

2.Using the mid function, the input formula is as follows:

Formula description: The first parameter of the mid function is the cell with the value, here is a1; The second parameter is to start from the digit, and the provinces all start from the third, so here is 3; The third parameter is to take a few digits, because the province in the cell is two words, so this parameter is 2

3.After dropping down the fill, the result is as follows: