Extracting numbers and letters from a string



I'm importing a column (from Information Link) that contains the following information:




Each one of the letters (I, J, K, L) denotes an action type and the number corresponds to how many times that action was taken. The total is the total number of actions taken.


I need to create 5 calculated columns: I, J, K, L and Total with each one of them containing the number of times that action was taken.

This imported column always has this same format, but the numbers will vary from row to row, ranging from 0 to 1000.

Can someone help me with this?



That works perfectly, Murari! Thank you very much.

My best regards,


rafaelbegosso - Apr 13, 2018 - 6:48am

Insert > Calculated Column...
    Name: I
    Expression: Mid([Column1],Find("I-",[Column1]) + 2,Find("J",[Column1]) - 1 - (Find("I-",[Column1]) + 2))
Insert > Calculated Column...
    Name: J
    Expression: Mid([Column1],Find("J-",[Column1]) + 2,Find("K",[Column1]) - 1 - (Find("J-",[Column1]) + 2))
Insert > Calculated Column...
    Name: K
    Expression: Mid([Column1],Find("K-",[Column1]) + 2,Find("L",[Column1]) - 1 - (Find("K-",[Column1]) + 2))
Insert > Calculated Column...
    Name: L
    Expression: Mid([Column1],Find("L-",[Column1]) + 2,Find("Total",[Column1]) - 1 - (Find("L-",[Column1]) + 2))
Insert > Calculated Column...
    Name: Total
    Expression: Mid([Column1],Find("Total-",[Column1]) + 6,Len([Column1]) - Find("Total-",[Column1]))

Let me know if this works for you.
Murari K - Apr 12, 2018 - 10:29pm
+ Add a Comment

(1) Answer