Help with a formula to extract all 8 character alpha-numeric IDs from an Excel column

[techie2]

I am new to this forum. I have seen some excellent suggestions and solutions being posted.

I have a spread sheet with Special Notes column which has notes text in each cell of the column. Each cell text contains one or more 8 character numeric and alpha numeric IDs. I need to extract all these IDs and dump them in a separate column on the spread sheet . I am giving examples of text from two cells in the column:

K1= Model Z behind model 8 (ABC Company) Please generate AMC 47052130 (replacing dialup AMC 47059003)
K2= Model J behind model 9 (XYZ Company) Please Generate AMC ID 4554G023 (replacing 4554A032)

Is there a formula I can use to extract those 8 character AMC IDs with or without AMC in front of them.

I am very excited to be on this forum.

Thanks in advance

Techie

 

[jamtay317]

where do these numbers come from? and can you please provide some sample data by selecting all borders and copy and pasting here.

 

[techie2]

Thanks for responding that quickly. These numbers are entered into notes via a Web application that are pulled from a SQL database into the Excel sheet. There will be hundreds of records and each note will have many 8 character IDs. I have an image file with spread sheet. How do I attach to this post?

 

[jamtay317]

you really cant attach any files here, but you can attach the Spreadsheet to Google docs and share the link here, that way we can see exactly what we are tring to do

 

[ZVI]

Hi Techie, welcome to MrExcel!

User Defined Function (UDF) can be used.
The usage with data of post #1:
=GetID(K1,1) returns "47052130" - the 1st occurrence of 8-digits string in K1
=GetID(K1,2) returns "47059003" - the 2nd occurrence of 8-digits string in K1

The code of UDF:

' Goal of UDF:
'   Extracting of 8-digits string from Txt argument
' Arguments:
'   Txt   - string being searched
'   Index - index of occurrence (1-based)
' Usage:
'   [K1] = "Model Z behind model 8 (ABC Company) Please generate AMC 47052130 (replacing dialup AMC 47059003)"
'   =GetID(K1,1) or =GetID(K1) Return: "47052130" - the 1st occurrence
'   =GetID(K1,2) Return: "47059003"  - the 2nd occurrence
Function GetID(Txt As String, Optional Index = 1) As String
  Dim a
  Static RegEx As Object
  If RegEx Is Nothing Then
    Set RegEx = CreateObject("vbscript.regexp")
    RegEx.Global = True
    RegEx.Pattern = "\d{8}"
  End If
  On Error Resume Next
  With RegEx
    If .Test(Txt) Then
      GetID = .Execute(Txt)(Index - 1)
    End If
  End With
End Function
 
Sub Test()
  Dim Txt As String
  Txt = "Model Z behind model 8 (ABC Company) Please generate AMC 47052130 (replacing dialup AMC 47059003)"
  Debug.Print GetID(Txt, 1) ' = "47052130" 
  Debug.Print GetID(Txt, 2) ' = "47059003"
End Sub

Regards

 

[techie2]

Thanks, again. I have uploaded the example spreadsheet to Google docs. Here is the link:
https://docs.google.com/file/d/0B01zn-L1t2Mza2xXaXcyS0lDUlU/edit?usp=sharing

The column 'B' and column 'D' contain the data which is brought in from SQL database for the Web Applications.
I need to extract the AMC IDs from column 'D' into column 'C' and then compare the AMC IDs in column 'B' with the results. This way I will know which AMC IDs are not included in the notes. In other words I will just eliminate duplicates from column 'B' and column 'C'. Hope this clarifies my intent.

The other option was to create a look up from column 'B' in column 'D' and identify the ones that are missing in 'D'.

 

[techie2]

Awesome! Thank you so much. I will definitely try that shortly. However, I forgot to clarify that each note might have multiple occurrences of the 8 character IDs. Would this UDF then work or it needs modification. Also, I understand that this may also pick up other 8 character strings from the notes text that are not IDs. However, I don't think a logic can be developed to eliminate that. Right. It should still serve my purpose I think : )

 

[ZVI]

To get the list of IDs use this modification of UDF and the formula =GetID(K1)

' Goal of the UDF:
'   Extracting of 8-digits string from Txt argument
' Arguments:
'   Txt   - string being searched
'   Index - (optional) index of occurrence, 1-based
' Return:
'   List of occurrences or Index-based occurrence
' Usage:
'   [K1] = "Model Z behind model 8 (ABC Company) Please generate AMC 47052130 (replacing dialup AMC 47059003)"
'   =GetID(K1)   Return: "47052130,47059003" - all occurrences
'   =GetID(K1,1) Return: "47052130" - the 1st occurrence
'   =GetID(K1,2) Return: "47059003" - the 2nd occurrence
Function GetID(Txt As String, Optional Index = 0) As String
  Dim x
  Static RegEx As Object
  If RegEx Is Nothing Then
    Set RegEx = CreateObject("vbscript.regexp")
    RegEx.Global = True
    RegEx.Pattern = "\d{8}"
  End If
  On Error Resume Next
  With RegEx
    If .Test(Txt) Then
      If Index = 0 Then
        For Each x In .Execute(Txt)
          GetID = GetID & x & ","
        Next
        GetID = Left$(GetID, Len(GetID) - 1)
      Else
        GetID = .Execute(Txt)(Index - 1)
      End If
    End If
  End With
End Function
 
Sub Test()
  Dim Txt As String
  Txt = "Model Z behind model 8 (ABC Company) Please generate AMC 47052130 (replacing dialup AMC 47059003)"
  Debug.Print GetID(Txt)    ' = "47052130,47059003"
  Debug.Print GetID(Txt, 1) ' = "47052130"
  Debug.Print GetID(Txt, 2) ' = "47059003"
End Sub

Regards

 

[Rick Rothstein]

Awesome! Thank you so much. I will definitely try that shortly. However, I forgot to clarify that each note might have multiple occurrences of the 8 character IDs. Would this UDF then work or it needs modification.

ZVI's code has a second an optional 2nd argument (an index) which allow you to specify which ID you want when there are more than one. Note... if you specify an index value for an ID that does not exist, the function returns an empty string ("").

Also, I understand that this may also pick up other 8 character strings from the notes text that are not IDs. However, I don't think a logic can be developed to eliminate that. Right. It should still serve my purpose I think : )

As design, ZVI's code will consider any 8-digit number to be an ID. The following much more compact code (it's a one-liner) uses the same indexing scheme as ZVI's UDF but it assumes that anything following the upper case letters AMC, with a space on either side of it, is an ID (the code will ignore 8-digit numbers not preceeded by [space]AMC[space])...

Function GetIDs(Txt As String, Optional Index = 1) As String
  GetIDs = Left(Trim(Split(Txt & Replace(Space(8 * Index), " ", " AMC "), " AMC ", , vbTextCompare)(Index)), 8)
End Function

 

[Rick Rothstein]

As design, ZVI's code will consider any 8-digit number to be an ID. The following much more compact code (it's a one-liner) uses the same indexing scheme as ZVI's UDF but it assumes that anything following the upper case letters AMC, with a space on either side of it, is an ID (the code will ignore 8-digit numbers not preceeded by [space]AMC[space])...

Function GetIDs(Txt As String, Optional Index = 1) As String
  GetIDs = Left(Trim(Split(Txt & Replace(Space(8 * Index), " ", " AMC "), " AMC ", , vbTextCompare)(Index)), 8)
End Function

Here is my approach, modified to return values in the same way as ZVI's modified code does (could not keep it a one-liner though)...

Function GetIDs(Txt As String, Optional Index = 0) As String
  Dim X As Long, IDs() As String
  IDs = Split(Txt & Replace(Space(8 * Index), " ", " AMC "), " AMC ", , vbTextCompare)
  If Index Then
    GetIDs = Left(Trim(IDs(Index)), 8)
  Else
    For X = 1 To UBound(IDs)
      GetIDs = GetIDs & ", " & Left(Trim(IDs(X)), 8)
    Next
    GetIDs = Mid(GetIDs, 3)
  End If
End Function