Task description

The Excel sheet below contains one row. The value is a string containing parentheses, an asterisk, commas, hyphens and numbers:

The task is to split all numbers away, count the left parentheses (‘(‘) and the hyphens (‘-‘) before each number. For instance, for number 1, there are altogether five left parentheses and one hyphen before it. Below is the expected result:

Directions

1.      Start esProc and create a new program cellset.

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-processed Excel file.

3.   Select A1 and press Ctrl+C to copy the Excel data.

4.   Switch back to esProc, select A1 and press Ctrl+V to paste data. Below is the cellset with pasted data:

5.      Write code for implementing the task:

Now let’s list the above code and explain it line by line:

A2   Split A1’s string to get all numbers.

A3   Split A1’s string to get substrings of all types.

A4   Find the index number for each of A2’s number from A3, and create a new table sequence.

A5   Count the left parentheses before each number to generate a new column.

A6   Count the hyphens before each number to generate a new column.

A7   Remove the index column from A6’s table sequence to get the desired table.

6.      Execute the script. Then you can click A7 to view the final result in the value viewing section on the right:

7.      Click the button highlighted by the circle to copy the result to clipboard.

8.      Switch back to Excel, select C2 and paste the result in. You can enter the column headers manually.

【Reference】 data.zip