Formula to extract codes from cell

rimrattlerla11

New Member
Joined
Jan 27, 2016
Messages
17
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
Code:
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
 
Upvote 0
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)
 
Upvote 0
All the data you showed is in one single cell, correct? If so, give this UDF (user defined function) a try...
Code:
[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.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top