Extract number of X digits from string

[samahiji]

Hey there,
I have a table that contains texts and numbers. I'm looking for a function to extract the only 3 or 4 digits number from string. there is no reference between cells expect the 3 or 4 digits numbers.
I need to add 0 if number is only 3 digits (similar to image attached).

 

[JvdV]

Can you confirm post #13 works or not? Would be interesting to see if this option (created in ms365) that has 2019 functions being used would still go bust. I suspect it should work fine.

 

[Peter_SSs]

No, there will be only one set of 3/4 digits in the string.

In that case you could change the udf by removing part of the Pattern line as follows

Function GetNum(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "\d{3,4}"
    If .Test(s) Then GetNum = Format(.Execute(s)(0), "0000")
  End With
End Function

.. and/or try the worksheet formula options in columns M:N, depending on if there will be any rows like 5 & 6 that do not contain a 3 or 4 digit section and what result you would want in that circumstance if data like that is possible.

samahiji.xlsm
	J	K	L	M	N
1	Group		Expect	Expect	Expect
2	04W180COMP		0180	0180	0180
3	04W2442 BOX		2442	2442	2442
4	26 W 1622 CARS		1622	1622	1622
5	AB34CD				#N/A
6					#N/A
7	TRAIN 1241		1241	1241	1241
8	New computer930 30-12-11 - Copy		0930	0930	0930
9	06W244 COMP 6-2-12.bbb		0244	0244	0244
10	27 W 1291 3-12'' New Com		1291	1291	1291
Extract Number (3)

Cell Formulas
Range	Formula
L2:L10	L2	=GetNum(J2)
M2:M10	M2	=IFNA(TEXT(LOOKUP(9999,--MID(J2,FIND("000",CONCAT(IFERROR(MID(J2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(J2))),1)*0," "))),{3,4})),"0000"),"")
N2:N10	N2	=TEXT(LOOKUP(9999,--MID(J2,FIND("000",CONCAT(IFERROR(MID(J2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(J2))),1)*0," "))),{3,4})),"0000")

 

[samahiji]

Peter
Perfect!
That's really wonderful.

What if I need to consider the two digits also, but ignore the first two digits (I may consider any digits after the letter W)
What would the three formulas look like in column L, M, N above?

06W24 COMP 6-2-12.bbb (Expect: 0024)
27 INPUT 1291 WO 99 3-12'' New Com (Expect: 0099)

 

[Peter_SSs]

Peter
Perfect!
That's really wonderful.

Good news.

What if I need to consider the two digits also, but ignore the first two digits (I may consider any digits after the letter W)
What would the three formulas look like in column L, M, N above?

06W24 COMP 6-2-12.bbb (Expect: 0024)
27 INPUT 1291 WO 99 3-12'' New Com (Expect: 0099)

This appears to be quite a different question. The original condition was that there would be only one 3/4 digit number in the text. In each of these two examples there are three 2-digit numbers in the text.
I think that we need more examples to show the variety of possible data and the expected outcomes and/or a clearer description of exactly which 2-digit number should be chosen when there are multiple in the text.