Try.........
B2, copied down :
=MID(A2,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17)),4)
Regards
Bosco
The Find function part return the 1st digit position of the string.Could u please explain how Find is working here ?
Your formula is used to extract separated numbers in a string, but the OP's data is in a whole number (not separated number).you can try this one also with Ctrl + Shift + Enter
=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A2)))-1))
This will take all the numbers together from text string
Option Explicit
Sub EXTRACT_NUM()
Dim r%: r = Cells(Rows.Count, 1).End(3).Row
Dim i%, Oui
Range("B2").Resize(r - 1).ClearContents
Dim Obj As Object
Set Obj = CreateObject("VBscript.regExp")
Obj.Pattern = "(\d{4})"
For i = 2 To r
If Obj.test(Range("a" & i)) Then
Set Oui = Obj.Execute(Range("a" & i))
Range("b" & i) = Oui(0).Value
End If
Next
Set Obj = Nothing
End Sub
=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),4)*1
or
=MID(A2,MIN(FIND({0,1,2,3,4},A2&"0123456789")-1),4)
=LOOKUP(99^99,--(0&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2)+1)))))
https://xucopt.io.construct.net/33/homebase_portal/dev_flyby_requests/7454individual
ili
https://xucopt.io.construct.net/33/homebase_portal/dev_flyby_requests/strong_7415
=IFERROR(IFERROR(MID($A2,FIND("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-0),1)+1,4)*1,MID($A2,FIND("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-1),1)+1,4)*1),RIGHT(TRIM(MID(A2,FIND("#",SUBSTITUTE(A2,"/","#",S2))+1,255)),4))*1
And this formula below returns the same result (if there are no more separated numbers. It actually returns the first number it finds in the text.
=LOOKUP(99^99,--(0&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2)+1)))))
Hi navic,However, if there may be another possibility of the source text. What if there is another number in the text of the web address?
e.g.
https://xucopt.io.construct.net/33/homebase_portal/dev_flyby_requests/7454individual
https://xucopt.io.construct.net/33/homebase_portal/dev_flyby_requests/strong_7415
Then we can use the ARRAY formula below
=IFERROR(IFERROR(MID($A2,FIND("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-0),1)+1,4)*1,MID($A2,FIND("#",SUBSTITUTE(A2,"/","#",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-1),1)+1,4)*1),RIGHT(TRIM(MID(A2,FIND("#",SUBSTITUTE(A2,"/","#",S2))+1,255)),4))*1
Could you explain what is the purpose to add "0&...
...then could you fix the problem?