Split a String by Comma and Get Desired Values for Each Substring

 

We have an Excel file as below:

id

value

*

ids

values

S2

ABCDE

*

S2,S6,S7,S4,S3

S3

AB

*

G2,S4

S6

ADE

*

G9,S6,G2,S3

S7

!TE

*

G2,G9

G2

D

*

G2

G9

GPES

*

S2,G9

&ALL

ALL

*

&ALL

id column and value column make a dictionary table. The task is to get values (corresponding multiple values) for ids (multiple ids). The expected result is as follows:

undefined

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 Excel and load add-in file ExcelRaq.xll through Option –> add-ins in Excel and click Go button on the dialog. The add-in file is located in [esProc installation directory \bin]. Related information is easy to get if you don’t know how to load the add-in.

3. Write script book2.dfx in esProc:

A

1

=arg2.split@c()

2

=A1.(arg1.select@1(~(1)==A1.~)(2)).concat@c()

A1Split string parameter arg2 into a sequence by comma. The parameter is the second one passed in from the Excel file, which is a string made up of cells in D2:D8. You can define a parameter in esProc through Program->Parameter.