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
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.
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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

VBA Code:
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
JKLMN
1GroupExpectExpectExpect
204W180COMP018001800180
304W2442 BOX244224422442
426 W 1622 CARS162216221622
5AB34CD  #N/A
6  #N/A
7TRAIN 1241124112411241
8New computer930 30-12-11 - Copy093009300930
906W244 COMP 6-2-12.bbb024402440244
1027 W 1291 3-12'' New Com129112911291
Extract Number (3)
Cell Formulas
RangeFormula
L2:L10L2=GetNum(J2)
M2:M10M2=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:N10N2=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")
 
Upvote 1
Solution
Peter
Perfect! (y)
That's really wonderful. :giggle:

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)
 
Upvote 0
Peter
Perfect! (y)
That's really wonderful. :giggle:
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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