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

 

[Peter_SSs]

Are you looking for a formula solution or vba?

 

[samahiji]

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.

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

 

[alansidman]

Power Query Solution

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"

 

[samahiji]

Thanks a lot.
I'm not quite familiar with power query, but can you modify my little power query to fits your code in:

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"

 

[alansidman]

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.

 

[Peter_SSs]

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
	J	K	L
1	Group		Expect
2	04W180COMP		0180
3	04W2442 BOX		2442
4	26 W 1622 CARS		1622
5	TRAIN 1241		1241
Extract Number

Cell Formulas
Range	Formula
L2:L5	L2	=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")

 

[Peter_SSs]

Anyone is fine

Then another option would be a user-defined function like this.

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
	J	K	L
1	Group		Expect
2	04W180COMP		0180
3	04W2442 BOX		2442
4	26 W 1622 CARS		1622
5	AB34CD
6
7	TRAIN 1241		1241
Extract Number (2)

Cell Formulas
Range	Formula
L2:L7	L2	=GetNum(J2)

 

[samahiji]

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.

 

[Peter_SSs]

Peter UDF solution works perfect.

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