Extract strings from cells that contain ####-##-###-#### or ############# where # is a digit (0-9) & "-" is hyphen character

[ellison]

Hi, we are trying to tidy up data where the part number has been jumbled into the rest of the text in the cell

Part numbers are denoted in 2 patterns. If # is digit [0-9] and "-" is the hyphen character:
1. ####-##-###-####
2. #############
Is there a way for excel to extract the references?
Please find a table below to give some examples of data:

line	Raw_Info	Result_1	Result_2	Result_3	Explanation
1	SC counted in 9999999999999 & 2244-99-999-9999	9999999999999	2244-99-999-9999		2 numeric references that match the 2 patterns we are after
2	location ref 9999999				1 numeric reference but it doesn't match either of the patterns
3	1600-42-123-4567, last checked 04/05/2022	1600-42-123-4567			1 numeric reference does match
4	faulty 8666-17-663-5897, try 6578865443742	8666-17-663-5897	6578865443742		2 numeric references that match
5	nothing in here at all				0 numeric references at all
6	found 4240-84-955-0147, lost 7946135710000, alt 7090-55-741-9631	4240-84-955-0147	7946135710000	7090-55-741-9631	3 numeric references that match the 2 patterns we are after

Huge thanks for any help

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	line	Raw_Info	Result_1	Result_2	Result_3
2	1	SC counted in 9999999999999 & 2244-99-999-9999	9999999999999	2244-99-999-9999
3	2	location ref 9999999
4	3	1600-42-123-4567, last checked 04/05/2022	1600-42-123-4567
5	4	faulty 8666-17-663-5897, try 6578865443742	8666-17-663-5897	6578865443742
6	5	nothing in here at all
7	6	found 4240-84-955-0147, lost 7946135710000, alt 7090-55-741-9631	4240-84-955-0147	7946135710000	7090-55-741-9631
Main

Cell Formulas
Range	Formula
C2:D2,C7:E7,C5:D5,C3:C4,C6	C2	=IFERROR(TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ","</m><m>"),"&","&amp;"),",","")&"</m></k>","//m[(number() and string-length()=13) or (contains(.,'-') and string-length()=16)]")),"")
Dynamic array formulas.

 

[ellison]

That's absolutely WONDERFUL, huge thanks.

[By the way, anybody that's using it, you may want to get rid of any brackets in your raw-info (square or normal) as they seem to disrupt the formula]

And thanks once again!!!

With Warm Regards

 

[Fluff]

Glad to help & thanks for the feedback.

 

[mohadin]

Hi VBA Option

Sub test()
    Dim i&, ii&
    With CreateObject("VBScript.RegExp")
        .Pattern = "(\d+(\-\d+)+)|(\d+){8,}"
        .Global = True
        For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            Set x = .Execute(Cells(i, 2))
            For ii = 0 To x.Count - 1:  Cells(i, ii + 3) = x(ii): Next
        Next
    End With
End Sub

 

[ellison]

VBA option also works beautifully! We are spoilt for choice for solutions!!!!