The last content of the excel column is extracted to the specified location

Excel can use VBA to obtain the last content of the column and extract it to the specified position.

Software version: office2007

Here's how:1Extract the last in column A into C1:, enter ** as follows:

sub m()

x = range("a65536").end(3).row 'Assign the row number of the last non-empty cell to the variable x

cells(1, "c") = cells(x, "a") 'Assign the last non-empty cell to cell A3

end sub

Run **, close the VBA interface, and return Excel to get the following result:

The old man watched it for a long time, but he really didn't understand...

The method of extracting part of the content in a column of Excel is as follows (take Excel 2019 version of Windows 10 system to extract content and put it in a txt file as an example):

1. Open the document, and then use the mouse to drag and drop the method to select the cells in a column that need to extract information.

2. Right-click the selected cell and click the copy button in the pop-up drop-down menu.

3. Open the txt file to which you want to extract the information.

4. Use the keyboard shortcut "Ctrl+V.""Paste the contents of the cell you just copied and save it.

It can be extracted with the right function.

Tool: Excel 2010 version.

1. As shown in the figure below, the last two digits in the column data should be extracted;

2. First of all, enter the formula of the right function in cell B1, =right(A1,2), the function of the right function is to take the specified sock dust number character from the right end of the string, the first parameter A1 in the formula represents the area of the number, the second parameter 2 means to take two digits, and press the Enter key to get the result;

3. Place the mouse in the lower right corner of cell B1, and the drop-down filling formula can quickly extract the last two digits in column A.

The specific steps are as follows:

The materials that need to be prepared are: computer, excel

1. Open the excel data table first.

2. Then enter the formula "=lookup(9E+307,A2:F2)" in the excel cell on the page and press enter.

3. After pressing enter on the page, you can get the result, click the square at the bottom right of the number to fill the cell downward.

4. Then after filling in the page, you can compare the data and see that all the results are correct.

Using the mid(text,start num,num chars) function, text represents a text string; start num indicates the specified start position; num chars indicates the number to be intercepted. For example, enter abcdef in cell A1 and =mid(A1,2,3) in cell B1Finally, BCD is displayed in cell B1.

Note that a space also counts as a character. For your question, let's say your current text is in cell b5. You can enter =mid(b5,1,1)&mid(b5,10,1) in another column b6, and it will be displayed in b6"Salty x"。

If you hold down the mouse and drag it down, the whole list of data you want will appear.

1. Open Excel** to extract data.

2. Click the mouse and select the data to be extracted.

3. Select "Data" in the menu bar, and then select "Column".

4. In the pop-up dialog box, click "Next".

5. Put a tick in the small box in front of "Other", and then in the blank box next to it, press "shift+8" and enter "*" to click Next.

6. Delete the content in the "target area" on the right, and then click the icon next to it.

7. At this time, the dialog box will be collapsed, and then click on the blank space of **, and the dotted box will appear, and then click the icon in the collapsed dialog box.

8. After the dialog box opens, click "Finish".

9. At this time, another dialog box will pop up, click "Yes".

10. After completion, the mixed data is divided into a column.

1. Open the document, the original ** is as follows.

2. Copy the data in the second column to "three copies" and take the field name.

3. Operate on column e data column: check CTRL+H--Find:* (Replace with: blank without filling in--replace all.)

4. Continue to select this column --Ctrl+H--Find:) Replace with: blank without filling in--Replace all.

5. Operate on column C: check CTRL+H--Find: * Replace with: blank without filling in--Replace all.

6. Operate on column D: check CTRL+H--Find:* Replace with: Blank without filling in--Replace all.

7. Continue to select the column - Ctrl+H - Find: (* is replaced with: blank without filling in Replace all.)

8. The effect is as follows.

=left(a1,3) A1 cell, take 3 characters from the left.

right(a1,3) A1 cell, take 3 characters from the right.

mid(A1,3,5) A1 cell, starting from the 3rd character, take 5 characters.

Quickly extract parts of a column of data.

It depends on the situation, and it can be done with a function such as left mid right.

You can use the right function.

This function needs two parameters when it is used, the first parameter is the string that needs to be truncated or the address of the cell where the string to be truncated is placed, and the second parameter is the number of character bits to be truncated. For example, =right(A1,2), the last two characters are truncated from the characters of cell A1.

Excel** how to extract the last four digits of the number.

1. The operating software used in the demonstration is Microsoft Office Home and Student Edition 2016, and the operation methods of other versions of Office or WPS are basically the same.

2. First open Excel Electronic, and then enter the data needed to demonstrate the operation of extracting the last line of data.

3. Then select cell B1 and insert the function "=lookup(9 9,b4:b17)", which means that you can find the maximum value in the area from b4 to b17.

4. After the input is completed, click OK to see that we have obtained the last row of data in the column.

5. Finally, we use the format fill handle to extract the last row of the rest of the data, place the mouse in the lower right corner of the b1 cell, and then drag and drop to the right to fill it.

1. If the data are all numeric values, you can refer to the formula =lookup(9e+307,a2:f2);

2. If the data is not a numeric value or not exactly a numeric value, you can use the formula =lookup(1,0 (a2:f2<>"a2:f2)。

A2 formula drop-down!

Let's assume that the data range is b:q

lookup(9e＋307,b2:q2)

=left(a1,1,1) a1 represents the cell you want to select.