How to split elements in the list after the first split?

[anakhol]

Hello there,

I have values in column that look like "CN 220xxxxx: 123,000; CN 220xxxxx: 123,000;..."

"CN 22042138: 4738,500; CN 22042179: 841,500; CN 22042180: 1122,000; CN 22042183: 4500,000"

For each row number of "elements" differ.

I need to sum 4738,500 + 841,500 + 1122,000 + 4500,000 and place the sum into a virtual column. How can I achieve it?

I tried to split the text, but couldn't do anything with the result list. There is no regexp, there is no map-reduce.

[dbaum]

Ideas:

• Try experimenting with one of the EXTRACT functions.
• Parse the list using Apps Script via a Call a script task.

[dbaum · Accepted solution]

And, here's a way to get a list of just the values following each colon:

SPLIT(INDEX(SPLIT(TEXT(SORT(SPLIT(SUBSTITUTE([Column], ":", ";"), "; "))), "CN "), 1), " , ")

[anakhol]

Thanks.

After small adaptations it works like a charm.

[Marc_Dillon]

Try with:   SUM( EXTRACT( 'NUMBERS' , [column] ) )