Extracting value from a cell containing string after particular word

Question

I have a big excel file where in one column it contains Json as shown below:

""wrtieSeg"": {
    ""ValueName"": ""Type1"",
    ""Div"": ""45"",
    ""Date"": ""08/06/2017"",
    ""fig"": ""0-100""
  },
  ""readSeg"": {
    ""ValueName"": ""Type2"",
    ""Div"": ""45"",
    ""Date"": ""08/06/2017"",
    ""fig"": ""980""
  }

Now i have two columns (Type1 and Type2) in my excel sheet where i want fig value like (i.e 0-100) in Type1 column, and fig value like (i.e 980) in Type2 column.

Excel by itself is a poor choice to be doing this. You need a JSON parser. VBA probably has support for this, so do it there. — Tim Biegeleisen, Mar 30 '18 at 08:04
I am a little confused as to the layout but can you read the JSON into a JSON object using https://github.com/VBA-tools/VBA-JSON and then simply parse the object for the fig elements? It is easy I am just not sure where I am getting the JSON from and is it properly formed? I'd say with that snippet alone it would be impossible to say how you would extract definitively (but someone may contradict me) — QHarr, Mar 30 '18 at 08:13
The JSON as it stands is not properly formed so maybe just use traditional split function to extract ? Read in value from sheet and use Split to isolate ""fig"" — QHarr, Mar 30 '18 at 08:17

Michał Turczyn · Answer 1 · 2018-03-30T10:55:54.373

Since your JSON is quite basic, little helper function and main function should be enough to parse it. Note that you don't have valid JSON in your example, as it's lacking closing bracket }. I added it in my example (and also edited your question to be correct).

This code you have to define in Module in your VBAProject (reference how to call VBA functions from sheet cells). Now, if your JSON is located in A1 cell and you want to extract value for Type1, then you have to use such formula: =ExtractFromJson(A1, "Type1").

Option Explicit
Public Function ExtractFromJson(json As String, valueName As String)
    'declare variables needed
    Dim elementStartIndex As Long, elementEndIndex As Long, element As String
    'loop through json, extracting elements
    Do While InStr(elementStartIndex + 1, json, "{") > 0
        'locate element by searching { and }
        elementStartIndex = InStr(elementStartIndex + 1, json, "{")
        elementEndIndex = InStr(elementStartIndex + 1, json, "}")
        'extract element and pass it to function
        element = Mid(json, elementStartIndex, elementEndIndex -  elementStartIndex + 1)
        ExtractFromJson = ParseElement(element, valueName)

        If ExtractFromJson <> "" Then
            Exit Function
        End If
    Loop
End Function

Function ParseElement(element As String, valueName As String) As String
    Dim valueNameIndex As Long
    'locate variable with specified name and take its value
    valueNameIndex = InStr(1, element, """""ValueName"""":") + 14
    If valueName = Trim(Replace(Mid(element, valueNameIndex, InStr(valueNameIndex, element, ",") - valueNameIndex), """", "")) Then
        valueNameIndex = InStr(1, element, """""fig"""":") + 8
        ParseElement = Trim(Replace(Mid(element, valueNameIndex, InStr(valueNameIndex, element, "}") - valueNameIndex), """", ""))
        Exit Function
    End If
End Function

Turczyn Thanks, but i want function something like `=ParseElement("Type1","A1)` than it give me value for Type1. — Roy1245, Mar 30 '18 at 10:17

score 0 · Answer 2 · answered Mar 30 '18 at 14:50

0

you could use:

Function ParseElement(myType As String, A1 As Variant)
    Dim v As Variant

    v = Split(Mid(A1, InStr(A1, myType)), """")

    ParseElement = v(Application.Match("fig", v, 0) + 3)
End Function

you may add some error handling should the user pass wrong myType parameter or the Json cell not contain it

answered Mar 30 '18 at 14:50

DisplayName

13,283
2
11
19

Not working properly, since i have json data in almost 25000 rows. – Roy1245 Mar 31 '18 at 11:31
I don't get you. You may want to edit and enhance your question so as to give all proper detals – DisplayName Mar 31 '18 at 11:33
I have a big json file in an excel sheet where for each unique identifier cell i have one json. I want the extract only two `fig` value where `ValueName` is `Type1` and `ValueName` is `Type2`. – Roy1245 Mar 31 '18 at 11:35
and what would have it to do with _"Not working properly, since i have json data in almost 25000 rows"_? – DisplayName Mar 31 '18 at 11:45

Extracting value from a cell containing string after particular word

2 Answers2