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

 

[AndrewP70]

Re: Help with a formula to extract numerical dimensions in excel

Dear All , many thanks for taking the time to help with my problem.
I have formatted the basis for an Ecat and would now like to extract the dimensions into separate columns.
Eg (A1)6203-2RS: BRG; Deep groove ball bearing; ID: 17mm, OD40mm, W, 12mm, (2) rubber seals, std clearance.
How can I return the vales after ID (the dimension)? The same will need to apply to OD and W into separate columns. The long text fields I have prepared as above, vary in length and the ID, OD, W will not be the same length. Lastly there could be : or ; after the ID, OD or W.
I have over 125000 lines to provide this dimension data for so any help will be greatly appreciated.
Many thanks,
Andrew

 

[István Hirsch]

Re: Help with a formula to extract numerical dimensions in excel

More examples should have been given.

If the data is in col A, write ID, OD, and W in B1, C1, D1, resp.(see the sheet below), enter the formula in B2 then copy across and down.

Excel Workbook
	A	B	C	D
1		ID	OD	W
2	6203-2RS: BRG; Deep groove ball bearing; ID: 17mm, OD40mm, W, 12mm, (2) rubber seals, std clearance	17	40	12
Sheet

 

[AndrewP70]

Hi and many thanks. Could I ask what 012 underscored means.
Regards,
Andrew

 

[ZVI]

Re: Help with a formula to extract numerical dimensions in excel

Dear All , many thanks for taking the time to help with my problem.
I have formatted the basis for an Ecat and would now like to extract the dimensions into separate columns.
Eg (A1)6203-2RS: BRG; Deep groove ball bearing; ID: 17mm, OD40mm, W, 12mm, (2) rubber seals, std clearance.
How can I return the vales after ID (the dimension)? The same will need to apply to OD and W into separate columns. The long text fields I have prepared as above, vary in length and the ID, OD, W will not be the same length. Lastly there could be : or ; after the ID, OD or W.
I have over 125000 lines to provide this dimension data for so any help will be greatly appreciated.
Many thanks,
Andrew

Hi Andrew,

Here is also the macro solution.
Select the source range and run this macro, dimentiones will be extracted into the next 3 columns

Sub ExtractDimensions()
  ' Select the source range and run this macro,
  ’ dimentiones will be on the next 3 columns
 
  Const MASK = "((ID)|(OD)|(W))([:;.,\s]?)+(\d+)"
 
  Dim a, b()
  Dim c As Long, i As Long, j As Long, r As Long
  Dim s As String
  Dim Rng As Range
 
  ' Limit selection by the used range to allow selection of the full column
  Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
  If Rng Is Nothing Then Exit Sub
 
  ' Copy values of the selected cells to the array a()
  With Rng
    a = .Value
    If Not IsArray(a) Then
      ReDim a(1 To 1, 1 To 1)
      a(1, 1) = .Value
    End If
  End With
 
  ' Prepare the output array b()
  ReDim b(1 To UBound(a), 1 To 3)
 
  ' Main
  With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "((ID)|(OD)|(W))([:;.,\s]?)+(\d+)"
    i = UBound(b, 2)
    For r = 1 To UBound(a, 1)
      s = a(r, 1)
      If Len(s) Then
        With .Execute(s)
          If .Count > i Then j = i Else j = .Count
          For c = 1 To j
            b(r, c) = .Item(c - 1).SubMatches(5)
            If c = UBound(b, 2) Then Exit For
          Next
        End With
      End If
    Next
  End With
 
  ' Copy the result into 3 next columns
  Rng.Offset(, 1).Resize(, 3).Value = b()
 
End Sub

 

[ZVI]

Re: Help with a formula to extract numerical dimensions in excel

In the above code this line: .Pattern = "((ID)|(OD)|(W))([:;.,\s]?)+(\d+)"
would be better replaced by that one: .Pattern = MASK

 

[ZVI]

Re: Help with a formula to extract numerical dimensions in excel

Improved version of the code – order of dimensions in the text does not matter

Sub ExtractDimensions1()
  ' Select the source range and run this macro,
  ' dimensions will be on the next 3 columns
 
  ' Code of dimensions
  Const ID = "ID", OD = "OD", W = "W"
 
  Dim a, b()
  Dim c As Long, i As Long, j As Long, k As Long, r As Long
  Dim s As String
  Dim Rng As Range
 
  ' Limit selection by the used range to allow selection of the full column
  Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
  If Rng Is Nothing Then Exit Sub
  
  ' Copy values of the selected cells to the array a()
  With Rng
    a = .Value
    If Not IsArray(a) Then
      ReDim a(1 To 1, 1 To 1)
      a(1, 1) = .Value
    End If
  End With
 
  ' Prepare the output array b()
  ReDim b(1 To UBound(a), 1 To 3)
 
  ' Main
  With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "((" & ID & ")|(" & OD & ")|(" & W & "))([:;.,\s]?)+(\d+)"
    i = UBound(b, 2)
    For r = 1 To UBound(a, 1)
      s = a(r, 1)
      If Len(s) Then
        With .Execute(s)
          If .Count > i Then j = i Else j = .Count
          For k = 1 To j
            Select Case .Item(k - 1).SubMatches(0)
              Case ID: c = 1
              Case OD: c = 2
              Case W:  c = 3
            End Select
            b(r, c) = .Item(k - 1).SubMatches(5)
            If k = UBound(b, 2) Then Exit For
          Next
        End With
      End If
    Next
  End With
 
  ' Copy result into the 3 next columns
  Rng.Offset(, 1).Resize(, 3).Value = b()
 
End Sub

 

[István Hirsch]

Hi and many thanks. Could I ask what 012 underscored means.
Regards,
Andrew

Hi Andrew,

If you are referring to the digits in quotes ("012...) concatenated to $A2:


It is assumed that the value, for example, of ID will be the number closest to the substring "ID" to the right. FIND function goes through the digits from 0 to 9 to find their location one by one, but if it does not find any digit from the ten listed, it gives #VALUE! error. To avoid this, all the digits searched for are joined to the end of the string for FIND function to find them if they do not occur in the original string. As these "extra" digits are at the end of the string, they will not hinder determining the location of the closest digit.

 

[ZVI]

Please take into account that for correct result of the formula solution there are should not be any "W" or "OD" before ID's number.
For example, for the text of specification in A1: … ; WOOD ; ... ; ID: 17mm, OD40mm, W, 12mm, (2) where "..." is for any text.
the formulas return 17 for all dimensions because "W" and "OD" are present in the word WOOD.

Such sort of the task is what regular expressions are for, and it is implemented in the macro of post #36.

 

[István Hirsch]

Please take into account that for correct result of the formula solution there are should not be any "W" or "OD" before ID's number.
For example, for the text of specification in A1: … ; WOOD ; ... ; ID: 17mm, OD40mm, W, 12mm, (2) where "..." is for any text.
the formulas return 17 for all dimensions because "W" and "OD" are present in the word WOOD.

Such sort of the task is what regular expressions are for, and it is implemented in the macro of post #36.

You are right, my formula in post #32 needs correcting.

 

[ZVI]

You are right, my formula in post #32 needs correcting.

Your formula is good and works well for the data presented by Andrew,
but who knows what is in those 125000 lines of data