title: Parse a String in Excel Vba
body: <p>I have a macro that send an XMLHTTP request to a server and it gets as response a <strong>plain text</strong> string, not a JSON format string or other standard formats (at least for what I know).</p>
  
  <p>I would like to parse the output string in order to access the data in an structured approach in the same fashion as the <em>parseJson</em> subroutine in this <a href="https://stackoverflow.com/a/30494373/2165759">link</a></p>
  
  <p>My problem is I am not good with regular expressions and I am not able to modify the routine for my needs.</p>
  
  <p>The string that I need to parse has the following structure:</p>
  
  <ol>
  <li>The string is a single line</li>
  <li>Each single parameter is defined by its parameter name the equal simbol, its value and ending with; <strong>"NID=3;"</strong> or <strong>"SID=Test;"</strong></li>
  <li>Parameter can be collected in "structures" starts and end with the symbol | and they are identified with their name followed by ; such as <strong>|STEST;NID=3;SID=Test;|</strong></li>
  <li>A structure can contain also other structures</li>
  </ol>
  
  <p>An example of a output string is the following</p>
  
  <pre><code>|KC;|AD;PE=5;PF=3;|CD;PE=5;HP=test;|CD;PE=3;HP=abc;|
  </code></pre>
  
  <p>In this case there is a macro structure <strong>KC</strong> which contains a structure <strong>AD</strong>. The structure <strong>AD</strong> is composed by the parameters <strong>PE</strong>, <strong>PF</strong> and 2 structures <strong>CD</strong>. And finaly the structures <strong>CD</strong> have the parameters <strong>PE</strong> and <strong>HP</strong></p>
  
  <p>So I would like to parse the string to obtain an <em>Object/Dictionary</em> that reflects this structure, can you help me?</p>
  
  <p><strong>Adds after the first answers</strong></p>
  
  <p>Hi all, thank you for your help, but I think I should make more clear the output that I would like to get.
  For the example string that I have, I would like to have an object with the following structure:</p>
  
  <pre><code>&lt;KC&gt;
      &lt;AD&gt;
          &lt;PE&gt;5&lt;/PE&gt;
          &lt;PF&gt;3&lt;/PF&gt;
          &lt;CD&gt;
              &lt;PE&gt;5&lt;/PE&gt;
              &lt;HP&gt;test&lt;/HP&gt;
          &lt;/CD&gt;
          &lt;CD&gt;
              &lt;PE&gt;3&lt;/PE&gt;
              &lt;HP&gt;abc&lt;/HP&gt;
          &lt;/CD&gt;
      &lt;/AD&gt;
  &lt;/KC&gt;
  </code></pre>
  
  <p>So I started to wrote a possible working code base on some hint from @Nvj answer and the answer in this <a href="https://stackoverflow.com/a/30494373/2165759">link</a></p>
  
  <pre><code>Option Explicit
  Option Base 1
  
  Sub Test()
  
    Dim strContent As String
    Dim strState   As String
    Dim varOutput  As Variant
  
    strContent = "|KC;|AD;PE=5;PF=3;|CD;PE=5;HP=test;|CD;PE=3;HP=abc;|"
    Call ParseString(strContent, varOutput, strState)
  
  End Sub
  
  Sub ParseString(ByVal strContent As String, varOutput As Variant, strState As String)
  ' strContent - source string
  ' varOutput - created object or array to be returned as result
  ' strState - Object|Array|Error depending on processing to be returned as state
  Dim objTokens As Object
  Dim lngTokenId As Long
  Dim objRegEx As Object
  Dim bMatched As Boolean
  
  Set objTokens = CreateObject("Scripting.Dictionary")
  lngTokenId = 0
  Set objRegEx = CreateObject("VBScript.RegExp")
  With objRegEx
      .Global = True
      .MultiLine = True
      .IgnoreCase = True
      .Pattern = "\|[A-Z]{2};"  'Pattern for the name of structures
      Tokenize objTokens, objRegEx, strContent, lngTokenId, bMatched, "str"
      .Pattern = "[A-Z]{2}=[^\|=;]+;" 'Pattern for parameters name and values
      Tokenize objTokens, objRegEx, strContent, lngTokenId, bMatched, "par"
  End With
  
  End Sub
  
  Sub Tokenize(objTokens, objRegEx, strContent, lngTokenId, bMatched, strType)
  Dim strKey        As String
  Dim strKeyPar     As String
  Dim strKeyVal     As String
  
  Dim strWork       As String
  Dim strPar        As String
  Dim strVal        As String
  Dim strLevel      As String
  
  Dim strRes        As String
  
  Dim lngCopyIndex  As Long
  Dim objMatch      As Object
  
  strRes = ""
  lngCopyIndex = 1
  With objRegEx
      For Each objMatch In .Execute(strContent)
          If strType = "str" Then
            bMatched = True
            With objMatch
                strWork = Replace(.Value, "|", "")
                strWork = Replace(strWork, ";", "")
                strLevel = get_Level(strWork)
                strKey = "&lt;" &amp; lngTokenId &amp; strLevel &amp; strType &amp; "&gt;"
                objTokens(strKey) = strWork
                strRes = strRes &amp; Mid(strContent, lngCopyIndex, .FirstIndex - lngCopyIndex + 1) &amp; strKey
                lngCopyIndex = .FirstIndex + .Length + 1
            End With
            lngTokenId = lngTokenId + 1
          ElseIf strType = "par" Then
  
            strKeyPar = "&lt;" &amp; lngTokenId &amp; "par&gt;"
            strKeyVal = "&lt;" &amp; lngTokenId &amp; "val&gt;"
            strKey = strKeyPar &amp; strKeyVal
            bMatched = True
            With objMatch
                strWork = Replace(.Value, ";", "")
                strPar = Split(strWork, "=")(0)
                strVal = Split(strWork, "=")(1)
                objTokens(strKeyPar) = strPar
                objTokens(strKeyVal) = strVal
                strRes = strRes &amp; Mid(strContent, lngCopyIndex, .FirstIndex - lngCopyIndex + 1) &amp; strKey
                lngCopyIndex = .FirstIndex + .Length + 1
            End With
            lngTokenId = lngTokenId + 2
  
          End If
      Next
      strContent = strRes &amp; Mid(strContent, lngCopyIndex, Len(strContent) - lngCopyIndex + 1)
  End With
  End Sub
  
  Function get_Level(strInput As String) As String
  
  Select Case strInput
    Case "KC"
    get_Level = "L1"
    Case "AD"
    get_Level = "L2"
    Case "CD"
    get_Level = "L3"
    Case Else
    MsgBox ("Error")
    End
  End Select
  
  End Function
  </code></pre>
  
  <p>This function creates a dictionary with an item for each structure name, parameter name and parameter value as shown in the figure
  <a href="https://i.stack.imgur.com/Cv0Zv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Cv0Zv.png" alt="enter image description here"></a>
  Thanks to the function <code>get_Level</code> the items associated to structures have a level that should help to preserve the original hierarchy of the data.</p>
  
  <p>So what I am missing is a function to create an object that has the original structure of the input string. This is what the <code>Retrieve</code> function do in this answer <a href="https://stackoverflow.com/a/30494373/2165759">link</a>, but I do not know how to adapt it to my case</p>