• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

String Search and Extract

Hi:

I have a string of data with 4 consecutive numbers embedded and would like to extract only the numbers. Any suggestions/help? See attached. Thanks in advance.

frank
 

Attachments

  • Sample.xlsx
    9.5 KB · Views: 16
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
 
Frank Bacchus

I like this formula to extract only numbers

=INT(NPV(-0.9,IFERROR(MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)/10,"")))

Decio
 
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
Your formula is used to extract separated numbers in a string, but the OP's data is in a whole number (not separated number).

So, adopt a shorter and effective formula to solve the OP's question is enough and don't overkilled.

Regards
Bosco
 
People can solve many challenges like this more easily with PowerQuery, yet to my surprise many users don't even know it's there on the ribbon.
  1. Load the data to PQ via load from Table/Range
  2. Add a custom column
  3. enter this formula: Value.FromText(Text.Select([Field 1],{"0".."9"})) **
  4. Close and load
62997

Okay, fair enough, you'd have some redundancy. Then why not delete "Field1" from the PQ?
62998

** To extract the number this part is sufficient Text.Select([Field 1],{"0".."9"}), yet it would be loaded as text.
 

Attachments

  • Copy of Sample42631.xlsx
    17.9 KB · Views: 4
Can you Try this Macro??
Code:
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
 
This formula given by Bosco can be as follows.
Code:
=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)

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.
Code:
=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)))))

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.
Code:
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

In this new case, the formulas above will not return the correct result.
Then we can use the ARRAY formula below
Code:
=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
 

Attachments

  • FrankBacchus-navic-42631.xlsx
    12.1 KB · Views: 7
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)))))

1] Some nice idea.

2] Could you explain what is the purpose to add "0&" in front of MID >> ......--(0&MID(A2......) ?

Regards
Bosco
 
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
Hi navic,

In the new case, when I put 4 digit number in front and the new number (33) at the back, that is :

https://xucopt.io.construct.net/7454individual/homebase_portal/dev_flyby_requests/33

https://xucopt.io.construct.net_7415/homebase_portal/dev_flyby_requests/strong/33

Your array formula appear failure, then could you fix the problem ?

Regards
Bosco
 
Last edited:
@Bosco
Code:
Could you explain what is the purpose to add "0&...
There is no specific purpose, zero is left during when I testing different functions .
MID function returns the same result (with zero and without zero)

Code:
...then could you fix the problem?
You are right. However, this formula is aimed at extracting a number at the end of a text.
Sorry, but I now have not time to try solve this problem, in a hurry to work.
 
Back
Top