Formula to extract codes from cell

[rimrattlerla11]

I have a list of raw data, from which I'm trying to extract whatever appears after the word "Code": in any one particular cell.

The sample below shows how the data appears in one particular cell. Each grouping is separated by {} and is semicolon delimited.

{ "category": AAA; "description":XYZ ; "Code": "2BZ"; "msrp": 200; "invoice": 160; CategoryIdList": 3 };
{ "category": BBB; "description":ABC ; "Code": "1AG"; "msrp": 100; "invoice": 80; CategoryIdList": 11 };
{ "category": CCC; "description":DEF ; "Code": "FWR-00"; "msrp": 20; "invoice": 16; CategoryIdList": 77 };
{ "category": DDD; "description":HIJ ; "Code": "PDW"; "msrp": 500; "invoice": 411; CategoryIdList": 55 };
{ "category": EEE; "description":KLM ; "Code": "LOS-01"; "msrp": 55; "invoice": 42; CategoryIdList": 12 };

The desired output from this formula should be this:
"2BZ";"1AG";"FWR-00";"PDW";"LOS-01"

Thanks in advance for any help you can provide!

 

[NARAYANK991]

Hi ,

I have no idea of a single formula which can do what you want ; the following code , though not the best , will do the job.

Private Sub simpleRegex()
'   include a reference to Microsoft VBScript Regular Expressions 1.0 or any higher version
    Dim strPattern As String
    Dim regEx As New RegExp
    Dim match As Variant, matches As Variant
    Dim strInput As String
    Dim Myrange As Range


    strPattern = """Code""" & ": " & ".* " & """msrp""" & ":"
    Set Myrange = ActiveSheet.Range("A2")


    If strPattern <> "" Then
       strInput = Myrange.Value


       With regEx
            .Global = True
            .IgnoreCase = False
            .Pattern = strPattern
       End With


       If regEx.Test(strInput) Then
          Set matches = regEx.Execute(strInput)
          For Each match In matches
              Retstr = Retstr & match.Value & "   "
          Next
          stroutput = Trim(VBA.Replace(VBA.Replace(Retstr, """Code""" & ": ", ""), """msrp""" & ":", ""))
          stroutput = Left(stroutput, Len(stroutput) - 1)
          MsgBox stroutput
       Else
          MsgBox ("Not matched")
       End If
    End If
End Sub

Note that in the References , you will have to include a reference to :

Microsoft VBScript Regular Expressions 1.0

or any other higher version ; on my computer , the only other version was 5.5

 

[admiral100]

Hi,

Formula version :

=LEFT(RIGHT(A1,(LEN(A1)-SEARCH("Code",A1,SEARCH(" ",A1,1)))-6),FIND(";",RIGHT(A1,(LEN(A1)-SEARCH("Code",A1,SEARCH(" ",A1,1)))-6))-1)

 

[Rick Rothstein]

All the data you showed is in one single cell, correct? If so, give this UDF (user defined function) a try...

[table="width: 500"]
[tr]
	[td]Function GetCodes(ByVal S As String) As String
  Dim X As Long, Codes() As String
  Codes = Split(S, """Code"": ")
  For X = 1 To UBound(Codes)
    GetCodes = GetCodes & Left(Codes(X), InStr(Codes(X), "; ") + 1)
  Next
  GetCodes = Left(GetCodes, Len(GetCodes) - 2)
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetCodes just like it was a built-in Excel function. For example,

=GetCodes(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.