Group codes and sum their values from a single text string

[Anonymous]

Hi,

I've got a text column with this format "Oper A - 8.5, Oper B - 3.0, Oper A - 2.5, Oper C - 5.0, Oper B - 5.0" to define the operations that happened in 24 hours. Basically time is separated by " - " from the operation code and each entry is separated by commas.

What I would like to have at the end is "Oper A - 11.0, Oper B - 8.0, Oper C - 5.0", meaning I would like to group the operation codes and sum their hours.

Anybody could suggest any approach to this? Thanks!

[Phil_Seamark]

Hi @Anonymous

First of all, try splitting the column into two columns.  I suggest doing this in the Query Editor.

Then group them in the Query Editor.  This should give you the output you are after.

[Anonymous]

Thank you for your reply Phil,

That part is easy, but the data I've got is a bit more complex. As I mention in my original post, there're several codes in the same text column, separated by commas, so splitting the columns will result in a variable number of extra columns. To get more context, see the screenshot of the column I'm taking about.

The number of codes could be different per row, and the times too.

Thank you! 

[Phil_Seamark · Accepted solution]

Hi @Anonymous

You can split those into their own rows using the same split function.  Just click the advanced option in the Split dialog and set something like this for your column.

[Anonymous]

Thank you @Phil_Seamark,

I think I had in my mind an easiest way to calculate "on-the-fly" (without creating extra columns) the values after the commas and group them by the operation codes. I'll try the splitting instead. Thank you again for your help!