Extracting multiple values from alphanumeric text based on set criteria

[chrispegg]

Hello there,

I have a set of data which contains part numbers in text fields. Every part number starts "288" and has 10 digits.

Because the part number can appear in several columns (but not explicitly) I have concatenated the relevant columns into one alphanumeric column.

What I want to do is have a formula that extracts every unique part number from the text and separates it with a comma. For example:


[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Part number 2881234567 & P/N2889876543[/TD]
[TD]2881234567,2889876543[/TD]
[/TR]
[TR]
[TD]ehwh2889999999[/TD]
[TD]2889999999[/TD]
[/TR]
[TR]
[TD]2888889991 or 2888889992 or 2888889993[/TD]
[TD]2888889991,2888889992,2888889993[/TD]
[/TR]
</tbody>[/TABLE]


I originally found =TRIM(LEFT(SUBSTITUTE(MID(12,FIND("288",A1),LEN(A1))," ",REPT(" ",100)),100)) which seemed to work, until I realised that it would stop after a space.

Is there any way around this?

 

[Eric W]

Consider:

	A	B	C
Part number 2881234567 & P/N2889876543	2881234567,2889876543	2881234567,2889876543
ehwh2889999999	2889999999	2889999999
2888889991 or 2888889992 or 2888889993	2888889991,2888889992,2888889993	2888889991,2888889992,2888889993

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=getparts(A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]{=TEXTJOIN(",",TRUE,IF(ISERROR(MID("x"&A1,ROW(INDIRECT("1:"&LEN(A1)-2)),1)+0)*(MID("x"&A1,ROW(INDIRECT("1:"&LEN(A1)-2))+1,3)="288"),MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1)-2))+1,10),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



A couple of options. If you have Excel 365 with the TEXTJOIN function, you can use the (somewhat awkward) formula in B1. If you don't have that, you may need a UDF (User defined function).

To install a UDF, open your workbook, and press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. Paste the following code into the window that opens:

Public Function GetParts(MyString As String) As String
Dim x As Variant
    
    With CreateObject("VBScript.RegExp")
        .Pattern = "288\d{7}"
        .Global = True
        For Each x In .Execute(MyString)
            GetParts = GetParts & "," & x
        Next x
    End With
    GetParts = Mid(GetParts, 2)

End Function

Close the editor by pressing Alt-Q. Now you can use the function in C1.

Hope one of these helps!

 

[Rick Rothstein]

Here is another UDF (user defined function) that you can consider...

Function GetParts(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Not Mid(S, X, 1) Like "#" Then Mid(S, X) = " "
  Next
  GetParts = Replace(Application.Trim(S), " ", ", ")
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 GetParts just like it was a built-in Excel function. For example,

=GetParts(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.