Formula to extract codes from cell

Thanks:  0
Likes:  0

# Thread: Formula to extract codes from cell

1. ## 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"

2. ## 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. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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