Formula to extract codes from cell
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Formula to extract codes from cell

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula to extract codes from cell

    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!

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Location
    CHENNAI , India
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to extract codes from cell

    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

  3. #3
    Board Regular
    Join Date
    Jan 2015
    Posts
    797
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to extract codes from cell

    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)

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,336
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Formula to extract codes from cell

    All the data you showed is in one single cell, correct? If so, give this UDF (user defined function) a try...
    Code:
    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
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •