Extract number of X digits from string

samahiji

Board Regular
Joined
Oct 6, 2015
Messages
82
Office Version
  1. 2019
Platform
  1. Windows
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).
 

Attachments

  • Ext_number_string.JPG
    Ext_number_string.JPG
    15.3 KB · Views: 56
That's good. (y)
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.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
Book1
AB
1GroupExpect
204R180COMP0180
306N2442 BOX2442
426 PP 1622 CARS1622
5TRAIN 12411241
6123 etc0123
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=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.
 
Upvote 0
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)
 
Upvote 0
One way with formula base:
Book1
JKL
1GroupExpect
204W180COMP0180
304W2442 BOX2442
426 W 1622 CARS1622
5TRAIN 12411241
608 W 1089 Computer Day-41089
714W490 3.5 Box 0490
8Table W 316 (09 03 11) 0316
915W928COMPUTER (2) 0928
1021W405COMP GL (1) (1) 0405
Sheet1
Cell Formulas
RangeFormula
L2:L10L2=TEXT(AGGREGATE(14,6,MID(J2& " ",ROW(INDIRECT("1:" & LEN(J2))),{4,3})+0,1),"0000")
 
Upvote 0
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
JKLM
1
208 W 1089 Computer Day-4 (1089)10891089
314W490 3.5 Box (0490)04900490
4Table W 316 (09 03 11) (0316)03160316
515W928COMPUTER (2) (0928)09280928
621W405COMP GL (1) (1) (0405)04050405
Sheet2
Cell Formulas
RangeFormula
L2:L6L2=GetNum(J2)
M2:M6M2=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")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top