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

 

[techie2]

Thank you Zvi and Rick. I will try these solutions on my worksheet tonight. I am a bit slow because I am not used to coding but I am really excited to find these programs on this forum.

 

[Rick Rothstein]

Thank you Zvi and Rick. I will try these solutions on my worksheet tonight. I am a bit slow because I am not used to coding but I am really excited to find these programs on this forum.

I thought about my code a little more and managed to make it three code lines shorter; the following produces the exact same results as my previous code...

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)
  For X = 1 To UBound(IDs)
    If Index = 0 Or Index = X Then GetIDs = GetIDs & ", " & Left(Trim(IDs(X)), 8)
  Next
  GetIDs = Mid(GetIDs, 3)
End Function

Not sure what you meant when you said "I am a bit slow because I am not used to coding", so I thought the following might be useful to you...

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 GetIDs just like it was a built-in Excel function. For example, use this to get the default action of returning all the IDs as a comma delimited text string...

=GetIDs(A1)

Or use this to return a particular, individual ID number (the second one for this example)...

=GetIDs(A1,2)

 

[techie2]

I used Zvi's modified UDF. It works but it does not pull alpha-numeric AMC IDs (only numeric ones). Here is the link to my test file:
https://docs.google.com/file/d/0B01zn-L1t2MzU2F6bVhrWEpPdjQ/edit?usp=sharing

Zvi, can we modify the function so that it extracts all 8 character strings from the specified cell.

 

[Rick Rothstein]

I used Zvi's modified UDF. It works but it does not pull alpha-numeric AMC IDs (only numeric ones). Here is the link to my test file:
https://docs.google.com/file/d/0B01zn-L1t2MzU2F6bVhrWEpPdjQ/edit?usp=sharing

Zvi, can we modify the function so that it extracts all 8 character strings from the specified cell.

Your link requires you to approve a fellow Google Drive accounts request to see your file. I sent that request to you and am awaiting your approval for me to see it. In the meantime, just guessing at what your worksheet might look like, I would encourage you to try my code (especially the code I just posted in Message #12) as it does not restrict the eight characters in your ID to be only digits... it returns the 8 characters following the space after the upper case AMC letters. Try it... I think you will probably find it does what you want (I'll know for sure once I can try it out on your actual data).

 

[techie2]

I also tried Rick's modified solution. It pulls both numeric and alpha numeric IDs. However, due to the criteria of 'space' AMC 'sapce' it thinks 'ID 47052' to be an 8 character string. using this criteria may also miss those IDs that are preceded by word 'AMC ID'. Wonder how we can workaround these. Here is my link to the test Excel file with Rick's solution...
https://docs.google.com/file/d/0B01zn-L1t2MzYmpVQjIxbDdlZDA/edit?usp=sharing

 

[Rick Rothstein]

I also tried Rick's modified solution. It pulls both numeric and alpha numeric IDs. However, due to the criteria of 'space' AMC 'sapce' it thinks 'ID 47052' to be an 8 character string. using this criteria may also miss those IDs that are preceded by word 'AMC ID'. Wonder how we can workaround these. Here is my link to the test Excel file with Rick's solution...
https://docs.google.com/file/d/0B01zn-L1t2MzYmpVQjIxbDdlZDA/edit?usp=sharing

One problem is you gave us example data that did not contain "AMC ID" and somehow expected us to know we would have to deal with that particular text string... we are good here, but not that good. See if this does what you want...

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

 

[techie2]

Hi Rick. You guessed it right. I wasn't sure how to create UDF. I found a tutorial that helped. Of course it was similar to what you posted. Thanks for your thoughtfulness. I have released the permission to view spreadsheets. I tried your function in #12. I still have the following issues.

I get 4554G023 form "Model J behind model 9 (XYZ Company) Please Generate AMC 4554G023 (replacing 4554A032)"
And
I get 'ID 47052', and 47059003 from "Model Z behind model 8 (ABC Company) Please generate AMC ID 47052130 (replacing dialup AMC 47059003)"

So, as I said before, the 8 character IDs are either preceded by AMC or AMC ID in some cases. Can we incorporate both criteria?

 

[Rick Rothstein]

I get 4554G023 form "Model J behind model 9 (XYZ Company) Please Generate AMC 4554G023 (replacing 4554A032)"

I do not understand why this is an issue... you said you wanted the eight characters following AMC (or AMC ID... see below) and those are the 8 characters following AMC. Please explain the problem.

And I get 'ID 47052', and 47059003 from "Model Z behind model 8 (ABC Company) Please generate AMC ID 47052130 (replacing dialup AMC 47059003)"

I am guessing with our messages crossing each other, you may have missed my posting in Message #16... the code I posted in it takes care of that issue.

 

[ZVI]

can we modify the function so that it extracts all 8 character strings from the specified cell.

Try this in code of post #8: RegEx.Pattern = " [0-9A-Z]{8}"
Because of using the static object don't forget to reset VBA-project or alternatively - save, close and reload it.

 

[TheCman81]

If you don't need the AMC or AMC ID you can use this formula approach

=MID(SUBSTITUTE(K1,"ID ",""),FIND("AMC",K1)+4,8)