title: Parse long string based with different character VBA
body: <p>I have broken my head. I need parse long string like that.</p>
  
  <pre><code>2003|Jaguar|S-Type|Base Sedan 4-Door|4.2L 4196CC V8 GAS DOHC Naturally Aspirated::Base To VIN # N52047 2003|Jaguar|S-Type|Base Sedan 4-Door|3.0L 183Cu. In. V6 GAS DOHC Naturally Aspirated::Base To VIN # N52047 2001|Jaguar|S-Type|Base Sedan 4-Door|4.0L 3996CC 244Cu. In. V8 GAS DOHC Naturally Aspirated::To VIN # N52047 2001|Jaguar|S-Type|Base Sedan 4-Door|3.0L 183Cu. In. V6 GAS DOHC Naturally Aspirated::To VIN # N52047 2002|Ford|Thunderbird 2002|Lincoln|LS 2002|Jaguar|S-Type|Base Sedan 4-Door|4.0L 3996CC 244Cu. In. V8 GAS DOHC Naturally Aspirated::To VIN # N52047 2000|Jaguar|S-Type|Base Sedan 4-Door|4.0L 3996CC 244Cu. In. V8 GAS DOHC Naturally Aspirated::To VIN # N52047 2002|Jaguar|S-Type|Base Sedan 4-Door|3.0L 183Cu. In. V6 GAS DOHC Naturally Aspirated::To VIN # N52047 2000|Jaguar|S-Type|Base Sedan 4-Door|3.0L 183Cu. In. V6 GAS DOHC Naturally Aspirated::To VIN # N52047 2000|Lincoln|LS 2003|Lincoln|LS 2001|Lincoln|LS 2003|Ford|Thunderbird 2004|Lincoln|LS 2004|Jaguar|S-Type|Base Sedan 4-Door|4.2L 4196CC V8 GAS DOHC Naturally Aspirated::Base To VIN # N52047 2004|Ford|Thunderbird 2005|Jaguar|S-Type|Sport Sedan 4-Door|3.0L 183Cu. In. V6 GAS DOHC Naturally Aspirated::Base / Sport To VIN # N52047 2005|Jaguar|S-Type|Base Sedan 4-Door|3.0L 183Cu. In. V6 GAS DOHC Naturally Aspirated::Base / Sport To VIN # N52047 2005|Lincoln|LS 2004|Jaguar|XJ8 2005|Jaguar|S-Type|Sport Sedan 4-Door|4.2L 4196CC V8 GAS DOHC Naturally Aspirated::Base / Sport To VIN # N52047 2006|Jaguar|S-Type|Base Sedan 4-Door|3.0L 183Cu. In. V6 GAS DOHC Naturally Aspirated::Base / VDP Edition To VIN # N52047 2006|Jaguar|S-Type|VDP Edition Sedan 4-Door|4.2L 4196CC V8 GAS DOHC Naturally Aspirated::Base / VDP Edition To VIN # N52047 2005|Jaguar|XJ8 2004|Jaguar|S-Type|Base Sedan 4-Door|3.0L 183Cu. In. V6 GAS DOHC Naturally Aspirated::Base To VIN # N52047 2006|Jaguar|S-Type|Base Sedan 4-Door|4.2L 4196CC V8 GAS DOHC Naturally Aspirated::Base / VDP Edition To VIN # N52047 2005|Ford|Thunderbird 2006|Lincoln|LS 2000|Jaguar|S-Type|Sport Sedan 4-Door|4.0L 3996CC 244Cu. In. V8 GAS DOHC Naturally Aspirated::To VIN # N52047 2002|Jaguar|S-Type|Sport Sedan 4-Door|4.0L 3996CC 244Cu. In. V8 GAS DOHC Naturally Aspirated::To VIN # N52047 2001|Jaguar|S-Type|Sport Sedan 4-Door|4.0L 3996CC 244Cu. In. V8 GAS DOHC Naturally Aspirated::To VIN # N52047 2002|Jaguar|S-Type|Base Sedan 4-Door|3.0L 2967CC 181Cu. In. V6 GAS DOHC Naturally Aspirated::To VIN # N52047 2005|Jaguar|S-Type|Sport Sedan 4-Door|3.0L 2967CC 181Cu. In. V6 GAS DOHC Naturally Aspirated::Base / Sport To VIN # N52047 2005|Jaguar|S-Type|Base Sedan 4-Door|4.2L 4196CC 256Cu. In. V8 GAS DOHC Naturally Aspirated::Base / Sport To VIN # N52047 2004|Jaguar|S-Type|Base Sedan 4-Door|3.0L 2967CC 181Cu. In. V6 GAS DOHC Naturally Aspirated::Base To VIN # N52047 2003|Jaguar|S-Type|Base Sedan 4-Door|4.2L 4196CC 256Cu. In. V8 GAS DOHC Naturally Aspirated::Base To VIN # N52047 2006|Jaguar|S-Type|Base Sedan 4-Door|3.0L 2967CC 181Cu. In. V6 GAS DOHC Naturally Aspirated::Base / VDP Edition To VIN # N52047 2004|Jaguar|S-Type|Base Sedan 4-Door|4.2L 4196CC 256Cu. In. V8 GAS DOHC Naturally Aspirated::Base To VIN # N52047 2005|Jaguar|S-Type|Sport Sedan 4-Door|4.2L 4196CC 256Cu. In. V8 GAS DOHC Naturally Aspirated::Base / Sport To VIN # N52047 2005|Jaguar|S-Type|Base Sedan 4-Door|3.0L 2967CC 181Cu. In. V6 GAS DOHC Naturally Aspirated::Base / Sport To VIN # N52047 2001|Jaguar|S-Type|Base Sedan 4-Door|3.0L 2967CC 181Cu. In. V6 GAS DOHC Naturally Aspirated::To VIN # N52047 2003|Jaguar|S-Type|Base Sedan 4-Door|3.0L 2967CC 181Cu. In. V6 GAS DOHC Naturally Aspirated::Base To VIN # N52047 2006|Jaguar|S-Type|Base Sedan 4-Door|4.2L 4196CC 256Cu. In. V8 GAS DOHC Naturally Aspirated::Base / VDP Edition To VIN # N52047 
  </code></pre>
  
  <p><a href="http://i.stack.imgur.com/DIsTC.jpg" rel="nofollow">Better structure</a></p>
  
  <p>I know that my final table has 6 columns
  3 -  (year, make, model) is required
  3 -  (trim, engine, notes) is optional</p>
  
  <p>Value engine is merged with Notes and has character "::"
  Others has character "|"</p>
  
  <p><a href="http://i.stack.imgur.com/IExPe.jpg" rel="nofollow">Final table</a></p>
  
  <p>Here is part of my code - it works wrong.  Any suggestion and improvement is welcomed and appreciated :)</p>
  
  <pre><code>Dim Ret
  Dim Ret2
  Dim strColumnA As String
  
  strColumnA = wsTestComp.Range("A1")
  Ret = Split(strColumnA, "|")
  j = 1
  k = 1
  For i = LBound(Ret) To UBound(Ret)
  
      Debug.Print Ret(i)
      If IsNumeric(Ret(i)) Then
          wsTestComp.Range("A2").Offset(k, j).value = Ret(i)
          j = j + 1
      Else
          If IsNumeric(Right(Ret(i), 4)) Then
          Ret2 = Split(Ret(i), "::")
          For h = LBound(Ret2) To UBound(Ret2)
              If IsNumeric(Right(Ret(i), 4)) Then
              wsTestComp.Range("A2").Offset(k, j).value = Left(Ret2(h), Len(Ret2(h)) - 5)
              Else
              wsTestComp.Range("A2").Offset(k, j).value = Ret2(h)
              j = j + 1
              End If
          Next h
  
          k = k + 1
          Else
          wsTestComp.Range("A2").Offset(k, j).value = Ret(i)
          j = j + 1
          End If
          End If
  
  Next i
  </code></pre>