Excel data wrangling issues. The program solution is also fine

Replace the "Carriage Return" with a comma, and replace the comma and -1 with a carriage return newline.

This way the Excel reading will become a line (with the original -1 as the demarcation), and if you don't think the comma is appropriate, you can also use other characters.

If you need to sort you can take the equal sign inside as the judgment symbol, add your replacement symbol, that is, you can get that pid sign, create an auxiliary column, and then you can sort, you can also vlook

The data you want to extract has a characteristic that there is a colon in the middle of the messy data": "This can be achieved by the following ideas:

1. Look for the text with ":" column b to return the left of the colon.

2. Column C returns the text to the right of the colon.

3. If you can't find the colon, the return is empty.

The first step is to locate the ":" location, find(":a4)

The second step is to return the position and all the text to the left: =left(a4,find(":a4))

Step 3: Determine the position of the colon from the right, = character length - position from the left = len(a4) - find(":a4)

Step 4: Return to the right side of the position: =right(a4,len(a4)-find(":a4))

The results and the state defense map are as follows:

Press alt+f11-insert-module-paste-save as macro-enabled format.

sub Extract data and light up ().

for i = 1 to [a65536].end(xlup).row

if instr(cells(i, 1), the unit project makes the name of the let") or instr(cells(i, 1), date of report") or instr(cells(i, 1), test report number") then

n = n + 1

cells(n, 5) =cells(i, 1)end if

nextend sub

Summary. Hello, dear, it's a pleasure to serve you here.

In Excel operations, if there is a mistake in the operation steps, you can make up for it by modifying the source program**.

Press Alt+F11 to open the macro in Excel;

2) Press Ctrl+R in the macro to open the Project Resource Management Panel (if it is opened, there will be no change after pressing Ctrl+R);

3) Find your file in the project resource management panel, click on it, and check whether there is "module n" (n is ......Right-click on these modules one by one, "Remove Module N"5) and finally save. ps: Make a final backup before the operation, delete the wrong one but can't be restored!

In Excel operations, if there is a mistake in the operation steps, you can make up for it by modifying the source program**.

Your question has been received, it will take a little time to type, please wait a while, please don't end the consultation, you can also provide more effective information, so that I can answer for you more coarsely and dry.

Hello, dear, I am glad to serve you here, I have found out for you in the excel operation, if the operation steps are wrong, you can make up for it by modifying the source program ** in excel press alt+f11 to open the macro; 2) Press Ctrl+R in the macro to open the Project Resource Management Panel (if it is opened, there will be no change after pressing Ctrl+R); 3) Find your file scrambled in the project resource management panel, click on it, and check whether there is "module n" (n is ......Right-click on these modules one by one, "Remove Module N"5) and finally save. ps:Make a final backup before operating Ying Xiangdan.,Delete the wrong thing but can't be recovered!