How to pull specific data from different length string?-CodePudding

I got this type of sport activity data in excel: I try to pull just a "calorie" value from column D to another cell. Any suggestions how to pull this data?

Tried to do it by =MID function, but those strings are different length. Any suggestions how to pull this data?

CodePudding user response：

You could try a formula like as below,

• Formula used in cell E1

=TEXTAFTER(TEXTBEFORE(D1,",""count"),"calorie"":") 0

Alternative approach, If you are not using MS365, then use the formula as shown below

• Formula used in cell F1

=REPLACE(LEFT(D1,FIND("""count",D1)-2),1,FIND("calorie",D1) 8,"") 0

Or, In MS365 using CHOOSECOLS() & TEXTSPLIT()

• Formula used in cell G1

=CHOOSECOLS(TEXTSPLIT(D1,{":",","},,1),8)/1

CodePudding user response：

=TEXTBEFORE(TEXTAFTER(A1,"""calorie"":"),",")

CodePudding user response：

Using VBA, you can use the Split method, since the pattern is the same:

Sub ExtractCalorie()
  Dim cel As String: cel = ActiveCell.Value
  'in the active cell is the string: {"duration":67,"altitude":0.0,"distance":21499,"calorie":89000,"count":0,"floor":0,"steps":0}
  Debug.Print Split(cel, ",")(3) 'it returns "calorie":89000
  'If only the value needed:
  Debug.Print Split(Split(cel, ",")(3), ":")(1) 'it returns 89000
End Sub

It can be easily transformed in a function, which works also as UDF, to be called from a formula in cell...