Task description

The following Excel file records a series of events:

We want to extract all dates from each row of the original data and put them in the next column separated by semicolon for further processing, as shown below:

Directions

1. Start esProc

 Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2. Open the above to-be-computed file in Excel.

3. Select the area of A1:A4, and press Ctrl+C to paste it to clipboard.

4. Open esProc, create a new dfx file, select and click cell A1 to place the caret above the editing box, and press Ctrl+V to paste data from the clipboard to A1, as shown below:

5. Go on to complete the script, as shown below:

6. Execute the script. Click A5 when the execution is over to view the final result on the right-hand value view section:

7. Click the “Copy data” button shown by the arrow above to paste A5’s result to clipboard.

8. Select cell B1 in Excel and press Ctrl+V to paste the result in. Since the returned result of multi-row text should correspond to column A row by row, you should do the paste without placing the caret above B1 in the editing status, which is quite the opposite of step 4.

Below is the esProc code:

A2 Split A1’s text into sequences by row and then split each row into a sequence of words.

A3 Convert all data that can be transformed into date type in the text string of each sequence according to the specific format.

A4 Select and the date data and split it away.

A5 Join up dates into string by semicolon row by row.

Note that A5 converts the date data into strings according to the date format configured on the Options window. To return a different type of date strings, you need to modify the value to the corresponding format, as shown in the following picture:

The above code shows how to copy data from Excel to esProc to handle and then paste the result back to Excel through a series of interface operations.

The steps are intuitive and easy to debug. If you can make sure that the code is correct and the operations are a part of your data analysis routine, you can write the code in the following way, where clipboard function is used:

Copy data from Excel and paste it to esProc to execute the script, and then copy the result and return to Excel to paste it in.

【Reference】 data.zip