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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Are you looking for a formula solution or vba?
 
Upvote 0
Anyone is fine, but I prefer formula.

I searched the forum and get the following code that help but needs to be modified to get numbers more than 3 digits.
The reason that the above function is a Dynamic Array which not accepted in table.

VBA Code:
Function JustNumbers(s As String) As Variant
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\D"
    JustNumbers = vbNullString
    If Len(Application.Trim(.Replace(s, " "))) > 0 Then JustNumbers = Split(Application.Trim(.Replace(s, " ")))
  End With
End Function
 
Upvote 0
Power Query Solution

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Group", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Group.1", "Group.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Group.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Group.2.1", "Group.2.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition1",{"Group.2.1"})
in
    #"Removed Other Columns"
 
Upvote 0
Thanks a lot.
I'm not quite familiar with power query, but can you modify my little power query to fits your code in:

Power Query:
let
    Source = Excel.Workbook(File.Contents("C:\Users\Test.xlsm"), null, true),
    Comp_Sheet = Source{[Item="Comp",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Comp_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Comp#", type any}, {"Path", type text}, {"Folder", type text}, {"File Name", type text}, {"File Extension", type text}, {"Data Created", type datetime}, {"Last Accessed", type datetime}, {"Last Modified", type datetime}, {"Size", Int64.Type}, {"Is Hidden", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([File Name], "W"))
in
    #"Filtered Rows"
 
Upvote 0
No I cannot. What would be helpful is if you uploaded your sample data that you are trying represent with XL2BB. Your Mcode does not appear to relate at all to the sample data that you provided and for which I supplied workable solution. Do not post a picture as that cannot be manipulated and I really don't like to spend my time recreating data you already have.
 
Upvote 0
I prefer formula.
Try this then. If it appears not to be working then try confirming it with Ctrl+Shift+Enter, not just Enter.

23 02 22.xlsm
JKL
1GroupExpect
204W180COMP0180
304W2442 BOX2442
426 W 1622 CARS1622
5TRAIN 12411241
Extract Number
Cell Formulas
RangeFormula
L2:L5L2=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
Anyone is fine
Then another option would be a user-defined function like this.

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

samahiji.xlsm
JKL
1GroupExpect
204W180COMP0180
304W2442 BOX2442
426 W 1622 CARS1622
5AB34CD 
6 
7TRAIN 12411241
Extract Number (2)
Cell Formulas
RangeFormula
L2:L7L2=GetNum(J2)
 
Upvote 1
alansidman

Peter UDF solution works perfect. I would like to have your power query solution as well.
here is my actual data. I can't use Xl2BB add-in as my machine is locked for security issue.
 

Attachments

  • DigArrFun2.JPG
    DigArrFun2.JPG
    33.1 KB · Views: 21
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
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