Extracting number between characters

[stupideye]

Hello,

I'm looking to extract product numbers to do some analysis. I get them in a format that is this:
/product/12345/product-description

The part I need is the "12345" (the product number). Sometimes the products are displayed like:
/product/12345?product-description
or
/product/12345

The product number is not always 5 characters either.

I've gotten close but I can't figure this one out! Any assistance? Thanks in advance.

 

[lrobbo314]

Does this UDF do what you're looking for?

Function EXTRACTNUM(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "/(\d+)\w?"
    EXTRACTNUM = .Execute(s)(0).submatches(0)
End With
End Function

 

[Eric W]

Welcome to the Board!

Book1
	A	B
1	/product/12345/product-description	12345
2	/product/12345?product-description	12345
3	/product/12345	12345
4	abcd1apr/xyz	43556
5	abc/12345/xyz/99999	99999
6	/product/1234567?description	1234567
Sheet3

Cell Formulas
Range	Formula
B1	=AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),{1,2,3,4,5,6,7,8,9})+0,1)

Here's one option. It has some limitations (see row 4). I'll see if I can devise something better, or you can try lrobbo's UDF. Let us know how these work.

 

[stupideye]

Welcome to the Board!

	A	B
/product/12345/product-description
/product/12345?product-description
/product/12345
abcd1apr/xyz
abc/12345/xyz/99999
/product/1234567?description

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

[TD="align: right"]12345[/TD]

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

[TD="align: right"]12345[/TD]

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

[TD="align: right"]12345[/TD]

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

[TD="align: right"]43556[/TD]

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

[TD="align: right"]99999[/TD]

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

[TD="align: right"]1234567[/TD]

</tbody>

Sheet3​

[TABLE="width: 514"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 502"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD]=AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),{1,2,3,4,5,6,7,8,9})+0,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Here's one option. It has some limitations (see row 4). I'll see if I can devise something better, or you can try lrobbo's UDF. Let us know how these work.

Does this UDF do what you're looking for?

Function EXTRACTNUM(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "/(\d+)\w?"
    EXTRACTNUM = .Execute(s)(0).submatches(0)
End With
End Function

A huge thank you to both of you for responding so quickly to my question. Like you mentioned Eric, the formula didn't quite work every time. The UDF ended up working perfectly though. Thank you again, this saved a huge amount of time.