Extracting numbers and letters from a string

Hello, 

 

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

 

I-100/J-50/K-40/L-5/Total-195

 

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?

Thanks!

2 Comments

That works perfectly, Murari! Thank you very much.

My best regards,

Rafael.

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

Login