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).

 

[samahiji]

That's good.
Wondering what went wrong, if anything, with the worksheet formula suggestion though?

Peter
The formula you provided works fine as well, however the data I have is constructed in a table and Excel doesn't support Dynamic array as part of a table.

 

[alansidman]

Too many # signs to understand. Really, can you just post the data in a table so I don't have to retype. Bedtime here in the mountains. I will check your response tomorrow for a workable file.

 

[JvdV]

Book1
	A	B
1	Group	Expect
2	04R180COMP	0180
3	06N2442 BOX	2442
4	26 PP 1622 CARS	1622
5	TRAIN 1241	1241
6	123 etc	0123
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=FILTERXML(CONCAT("<t><s>",IFERROR(--MID([@Group],ROW(A$1:INDEX(A:A,LEN([@Group]))),1),"</s><s>"),"</s></t>"),"//s[.>99][.<10000][1]")

I suppose for Excel 2019 this needs to be array entered. Also, use actual number formatting "0000" to keep numeric data.

 

[samahiji]

Peter
Why the function excludes the following strings (Return null):

08 W 1089 Computer Day-4 (1089)
14W490 3.5 Box (0490)
Table W 316 (09 03 11) (0316)
15W928COMPUTER (2) (0928)
21W405COMP GL (1) (1) (0405)

 

[bebo021999]

One way with formula base:

Book1
	J	K	L
1	Group		Expect
2	04W180COMP		0180
3	04W2442 BOX		2442
4	26 W 1622 CARS		1622
5	TRAIN 1241		1241
6	08 W 1089 Computer Day-4		1089
7	14W490 3.5 Box		0490
8	Table W 316 (09 03 11)		0316
9	15W928COMPUTER (2)		0928
10	21W405COMP GL (1) (1)		0405
Sheet1

Cell Formulas
Range	Formula
L2:L10	L2	=TEXT(AGGREGATE(14,6,MID(J2& " ",ROW(INDIRECT("1:" & LEN(J2))),{4,3})+0,1),"0000")

 

[samahiji]

=TEXT(AGGREGATE(14,6,MID(J2& " ",ROW(INDIRECT("1:" & LEN(J2))),{4,3})+0,1),"0000")

Nice, but it doesn't work for:

New computer930 30-12-11 - Copy
06W244 COMP 6-2-12.bbb
27 W 1291 3-12'' New Com

 

[Peter_SSs]

Peter
Why the function excludes the following strings (Return null):

Which function and what are the expected results and why?
What is wrong with these results?

samahiji.xlsm
	J	K	L	M
1
2	08 W 1089 Computer Day-4 (1089)		1089	1089
3	14W490 3.5 Box (0490)		0490	0490
4	Table W 316 (09 03 11) (0316)		0316	0316
5	15W928COMPUTER (2) (0928)		0928	0928
6	21W405COMP GL (1) (1) (0405)		0405	0405
Sheet2

Cell Formulas
Range	Formula
L2:L6	L2	=GetNum(J2)
M2:M6	M2	=TEXT(AGGREGATE(14,6,RIGHT(LEFT(J2,LOOKUP(1,-MID(J2,ROW(INDEX(A:A,1):INDEX(A:A,99)),1),ROW(INDEX(A:A,1):INDEX(A:A,99)))),{3,4})+0,1),"0000")

 

[samahiji]

Peter
I found that if there is any number exists after the combination of 3 or 4 digits GetNum will return null:

08 W 1089 Computer Day-4
14W490 3.5 Box
Table W 316 (09 03 11)
15W928COMPUTER (2)
21W405COMP GL (1) (1)

Sorry I put in my example numbers between parentheses () to show the value I want.

 

[Peter_SSs]

I found that if there is any number exists after the combination of 3 or 4 digits GetNum will return null:

That is correct, because for all your sample data in post #1, the 3 or 4 digit numbers were the last numbers in the cell. I used that 'fact' to construct my suggestion.

So, is it possible that your data contains more than one set of 3 or 4 (or more) digit numbers, and if so, which numbers should it choose?

For example, what is the expected result from this, and why?
ABC 123 DEF 4567 GHIJ 8901234 XYZ 888 XX

 

[samahiji]

That is correct, because for all your sample data in post #1, the 3 or 4 digit numbers were the last numbers in the cell. I used that 'fact' to construct my suggestion.

So, is it possible that your data contains more than one set of 3 or 4 (or more) digit numbers, and if so, which numbers should it choose?

For example, what is the expected result from this, and why?
ABC 123 DEF 4567 GHIJ 8901234 XYZ 888 XX

No, there will be only one set of 3/4 digits in the string. the rest of numbers are construction of date or random numbers (1/2 digits only).