Extract variable alphanumeric thread from alphanumeric thread

[LDNeedsHelp]

Hi there,

I have a list of part descriptions that I need to extract part numbers from. These part numbers may be purely numeric, alphanumeric, or contain hyphens, underscores or special characters:
e.g.

Descriptions	Part numbers
Handle #09-ir2135ti	#09-ir2135ti
D90-330-EX battery	D90-330-EX
VH202-02 steering wheel	VH202-02
4964237 wiring teddy	4964237
BPLR8CN 5" screw	BPLR8CN

<tbody>
</tbody>

I need something to extract:
1. string of numbers (if only numbers need >4 numbers to make a string)
2. string of numbers and letters or special characters

I'm hoping the logic can look for strings between spaces that include both letters and numbers to distinguish them from the text...

Would really appreciate any guidance anyone can offer!

Many thanks in advance.

 

[DRSteele]

I think this might help. I added some extra sample records for testing purposes. One formula relies on helper columns (Columns B through E) and the other is a standalone. Both formulas report text only.

It will only work for Descriptions that have one, two or three spaces and only for Descriptions that contain at least one number; it does not matter whether the unwanted text comes first or last.

Note: this text editor is messing up when displaying the formulas.<c2,mid(a2,1,c2-1),mid(a2,aggregate(14,6,c2:e2,1)+1,999))
I'll make two posts.<c2,mid(a2,1,c2-1),mid(a2,aggregate(14,6,c2:e2,1)+1,999))

</c2,mid(a2,1,c2-1),mid(a2,aggregate(14,6,c2:e2,1)+1,999))
</c2,mid(a2,1,c2-1),mid(a2,aggregate(14,6,c2:e2,1)+1,999))

 

[DRSteele]

Let's see if this works right.

	A	B	C	D	E	F	G
1	Descriptions	first number	first space	second space	third space	helper columns Part numbers	Part numbers
2	Handle #09-ir2135ti	9	7	#VALUE!	#VALUE!	#09-ir2135ti	#09-ir2135ti
3	D90-330-EX battery	2	11	#VALUE!	#VALUE!	D90-330-EX	D90-330-EX
4	VH202-02 steering wheel	3	9	18	#VALUE!	VH202-02	VH202-02
5	4964237 wiring teddy	1	8	15	#VALUE!	4964237	4964237
6	BPLR8CN 5" screw	5	8	11	#VALUE!	BPLR8CN	BPLR8CN
7	johnson rod H202-05	14	8	12	#VALUE!	H202-05	H202-05
8	johnson rod long H202-06b	19	8	12	17	H202-06b	H202-06b
9	XH202-02 steering wheel wood	3	9	18	24	XH202-02	XH202-02

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula C2 =SEARCH(" ",A2) D2 =SEARCH(" ",A2,C2+1) E2 =SEARCH(" ",A2,D2+1) F2 =IF(B2<C2,MID(A2,1,C2-1),MID(A2,AGGREGATE(14,6,C2:E2,1)+1,999)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B2 {=MATCH(TRUE,ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),0)} G2 {=IF(MATCH(TRUE,ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),0)<SEARCH(" ",A2),MID(A2,1,SEARCH(" ",A2)-1),MID(A2,MAX(IFERROR(SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2)+1)+1),0),IFERROR(SEARCH(" ",A2,SEARCH(" ",A2)+1),0),SEARCH(" ",A2))+1,999))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[DRSteele]

The formula in F2 should display as b2 < A2 at the start.

 

[mikerickson]

I think this UDF will do what you want.
Note that the optional incidence augment allows you to extract more than one part number from a string if it exists.

Function PartNumber(aString As String, Optional Incidence As Long = 1) As String
    Dim Words As Variant, oneWord As String
    Dim i As Long
    Words = Split(aString, " ")
    For i = 0 To UBound(Words)
        oneWord = LCase(Words(i))
        
        If Not (oneWord Like "*[!a-z]*") Then
            Rem word is all letters, reject
        ElseIf Not (oneWord Like "*[!0-9]*") Then
            Rem word is all numerals
            If Len(oneWord) < 4 Then
                Rem word is 1, 2 or 3 digit numeral, reject
            Else
                Rem long numberal, accept
                Incidence = Incidence - 1
            End If
        ElseIf oneWord Like "*[a-z]*" Then
            Rem contains at least one letter, accept
            Incidence = Incidence - 1
        End If
        If Incidence = 0 Then
            PartNumber = Words(i)
            Exit For
        End If
    Next i
End Function

 

[LDNeedsHelp]

Thank you Mike, this is exactly what I need!