Problem description

Here is an Excel file that has a 4-field data table where the fields contain different data. ID field contains values of {key}value structure, as shown below:

1. Get values from each row of ID whose keys are S1, S6, and S10 and add them after corresponding TEAMS field value; 2. Replace the asterisk (*) connector in each NUM value with the comma (,) and add the before each DETAILS field value. Below shows the source data and the desired result, where the contents highlighted in red are data before and after the arrangement:

Solution

Use the SPL XLL plug-in of Excel

Write the formular in a blank cell:

=spl("=E(?).run(replace(ID,[""{"",""}""],["""",""=""]):KV,TEAMS=TEAMS+"";""+[""S1"",""S6"",""S10""].(KV.property@c(~)).concat("";""),DETAILS=""[NUM -""+replace(NUM,""*"","","")+""].""+DETAILS)",A1:D3)

As shown:

Explanation:

Remove the left braces from the content of ID field and replace the right braces with the equal sign = to arrange the KV field content into the standard Key=value structure.

Use property function to get key values from each standard Key=value string and add them after TEAMS field values. The first step of data arrangement is thus finished.

Replace connectors in NUM field values with commas and put them to corresponding DETAILS values. The second step of data arrangement is thus finished.

Q & A Collection

https://stackoverflow.com/questions/61716625/copy-modify-the-copied-content-and-paste