Here is a quick homework to keep you busy this weekend.
Can you extract number of days from below text.
Nov15 PUTS (23 days)
March15 TIKS (3 days)
March1 TIKS (25 days)
June11 TIKS (10 days)
Assume the data is from cell A1.
Your solution should return the following:
23
3
25
10
Post your answers (formulas, VBA code or Power Query M code) in the comments.
Thanks to Kimdom for sending this question.
If you think this is too easy, here is a twist:
Try to solve this using formulas, but you are not allowed to use FIND or SEARCH formulas.
So go ahead and post your answers.
Flex your Excel muscles, solve these problems too:
Check out these Excel problems and challenges to workout that big fat Excel muscle in your brain.
115 Responses to “Can you extract numbers from text – homework”
=SUBSTITUTE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)," days",)
As the question mentioned not to use Find/Search, here we go:
=IF(MID(LEFT(A1,LEN(A1)-6),LEN(LEFT(A1,LEN(A1)-6))-1,1)="(",RIGHT(LEFT(A1,LEN(A1)-6),1),RIGHT(LEFT(A1,LEN(A1)-6),2))
Flash Fill = Problem Solved
=VALUE(MID(A1,FIND("(",A1)+1,2))
note: assumes there is always a space after a single digit number, and number is in no case no longer than 2 digits.
Without using FIND:
=VALUE(-RIGHT(SUBSTITUTE(A1,"days",""),5))
note: assumes format is always "(x days)", where x is a number
My approach:
=-RIGHT(SUBSTITUTE(A1," days",""),4)
With a maximum of 99 days.
A more flexible formula:
=AVERAGE(IFERROR(-RIGHT(SUBSTITUTE(A1," days",""),ROW($1:$10)),""))
The last formula is an array formula, of course.
Cool trick buddy!!
Very cool! Congratulations!
Awesome use of - and ()s. Thanks for sharing it.
hi xlarium,
regarding yr first formula, when I wirte it without minus (-) in front of RIGHT (), it gives me "ays)" as a result.
but when I put minus, as you did, it gives #Value error.
Can you please clarify ?
There is a space before the word 'days' in his formula, that's crucial...
=-RIGHT(SUBSTITUTE(A1,”days”,””),4)
The reason why you got the error #VALUE with the minus sign (if you omitted the space before 'days') is because "ays)" is not a number, obviously, and thus can not be converted into a real numerical value.
sorry, the comment system messed up my previous message....
His formule is:
=-RIGHT(SUBSTITUTE(A1,”[ONE SPACE HERE]days”,””),4)
😉
My formula doesn't need the space in the formula, but it does take 5 characters (thus including the space) instead of 4 with xlarium's variation...
The minus sign makes the negative number (Excel considers numbers between brackets being negative) a positive number....
My formula does:
"blahblah (23 days)" => remove "days" => "blahblah (23[SPACE])" => grab last 5 characters => "(23[SPACE])" => convert negative number to positive numerical value => 23
xlarium's does:
"blahblah (23 days)" => remove "[SPACE]days" => "blahblah (23)" => grab last 4 characters => "(23)" => convert negative number to positive numerical value => 23
Thanks for sharing the minus (-) sign and () concept. I think excel is not just about the formular but a great deal of the logic to apply in solving your problem.
As a matter of fact, FLASH FILL gave no sweat!
Cheers all!
My formula attempt:
=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),20)),"(","")," days)","")
You can used Delimiter (Text to Columns) two times:
1. Delimit using option Other - (
2. Delimit using Space.
Much more easier than cracking the head in writing the formulas.
Exactly. Simple and straight to the point!
Formula:
=SUBSTITUTE(LEFT(RIGHT(Text,8),2),"(","")
I like that.
Short and elegant and will even work with "blahblah (1 day)".
(I would only add VALUE() in the end to really make it into a number instead of text)
.... if you change the 2 with 3
TWO "minus signs" will be sufficient (instead of VALUE)
My formula:
=MID(A1,FIND("(",A1)+1,FIND("*",SUBSTITUTE(A1," ","*",3))-FIND("(",A1)-1)*1
Note: 1. FIND("(",A1)+1 is the cursor position where "(" ends and number starts
2. Substitute replaces 3rd space with a star(*) and finds its position.
=IF(ISNUMBER(VALUE(MID(A1,LEN(A1)-7,2))),VALUE(MID(A1,LEN(A1)-7,2)),VALUE(MID(A1,LEN(A1)-6,2)))
Another approach: =MID(A1,FIND("(",A1)+1,FIND(" days)",A1)-FIND("(",A1)-1)
Similar to a question that came up on Superuser yesterday.
http://superuser.com/questions/993442/how-can-i-sum-a-column-with-cells-containing-text/993446#993446
This should work:
=NPV(-0.9,,IFERROR(MID(RIGHT(A1,8),1+LEN(RIGHT(A1,8))-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))
interesting solution... 🙂
The RIGHT(A1,8) bit is a bodge for this scenario where you want some numbers, but not all numbers. But if you swap that for A1 it's a very handy formula to strip any string of letters and only leave the numbers.
The main interest of this formula is the intelligent use of NPV formula. Lesson : the knowledge of the algorithm behind the Excel formula is an asset for building out of the box formula.
Thanks for sharing.
This only works in Excel 2007 or above
A very old discussion for the same is stored here!
http://forum.chandoo.org/threads/removing-all-charcters-from-cell-but-numbers.13352/
& detailed ...
http://excelxor.com/2014/11/01/extracting-numbers-from-a-string-4-all-numbers-to-a-single-cell/
=VALUE( MID( [@Data]; SEARCH( " ("; [@Data]) + 2; SEARCH( " days)"; [@Data]) - SEARCH( " ("; [@Data]) - 2))
VBA approach from Rick Rothstein:
Function SumParens(S As String) As Variant
Dim X As Long, Parts() As String
If Len(S) Then
Parts = Split(Replace(S, ")", "("), "(")
For X = 1 To UBound(Parts)
SumParens = SumParens + Val(Parts(X))
Next
Else
SumParens = ""
End If
End Function
http://www.mrexcel.com/forum/excel-questions/56329-finding-value-between-parentheses.html
For the quick approach I would copy the column, then in that copied column use Find and replace doing two things:
- replace *( with nothing and then
- replace "space"* with nothing.
=VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(Text," days)",),"(",),2))
=IF(LEFT(RIGHT(A1,8),1)="(",VALUE(LEFT(RIGHT(A1,7),1)),VALUE(LEFT(RIGHT(A1,8),2))
Function getDays(myCell As Range)
n = InStr(myCell.Value, "(")
m = InStrRev(myCell.Value, " ")
getDays = Mid(myCell.Value, n + 1, m - n - 1)
End Function
Quite universal formula as long as there is only one number in the string, we would like to extract - found on MrExcel probably 🙂 :
=SUMPRODUCT(MID(0&A1;LARGE(INDEX(ISNUMBER(--MID(A1;ROW($1:$25);1))*ROW($1:$25);0);ROW($1:$25))+1;1)*10^ROW($1:$25)/10)
Yes, of course: LARGE()...doh! Thanks Jojek!
I have some sheet where I was looking to do something like that, but couldn't figure out how... function LARGE() is the answer 😀
But I don't get it why INDEX(x;0) is used in this formula? Doesn't it return exactly the same x matrix in this case?
Also using SUMPRODUCT() while you do the actual multiplying yourself (because you need to convert the text numbers to real numbers) seems overkill. SUM() should be working just fine in that case (and in theory faster).
So, this should be doing the same thing, but slightly shorter:
=SUM(MID(0&A1;LARGE(ISNUMBER(–MID(A1;ROW($1:$25);1))*ROW($1:$25);ROW($1:$25))+1;1)*10^ROW($1:$25)/10)
A VBA solution using Regular Expression :
Function ExtractNumber(str As String)
With CreateObject("vbscript.regexp")
.Pattern = "^(.*)(\()(\d+)(.*)"
ExtractNumber = IIf(.test(str), .Replace(str, "$3"), "")
End With
End Function
JeanMarc,
I'm not able to get your function work. I'm using it like this: "=ExtractNumber(B7)" because my data range starts in B7. I'm getting no output. Any ideas?
It works perfectly, but:
1) He uses late binding, so first make sure that "vbscript.regexp" is a registered library on your computer.
2) Also, the function needs to be in a module (not in the VBA code of the sheet or workbook).
3) Make sure you change all the parenthesis to proper parenthesis in your code. Just copy/pasting from this blog will not work.
😉
=VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,RIGHT(A1,6),""),"(",""),2))
Hilariously 90% of these formulas are completely wrong.
=LEFT(REPLACE(A1,1,FIND("(",A1,1),""),2)
That works 100% of the time.
Not if the number of days is greater than 99.
=LEFT(RIGHT(A1,(LEN(A1)-FIND("(",A1))),LEN(RIGHT(A1,(LEN(A1)-FIND("(",A1))))-6)
Simple solution without the need for any formulas etc.
Highlight the four cells A1:A4 then use Text to Columns with delimiters of Space and Other of Open Brackets - click on finish and in column C will be the numbers as required.
Enjoy :¬)
=IF(ISERROR(LEFT(RIGHT(A1,8),2)*1)=FALSE,LEFT(RIGHT(A1,8),2)*1,LEFT(RIGHT(RIGHT(A1,8),7),1)*1)
I know very crude but hope it works
=""&(0+RIGHT((SUBSTITUTE(SUBSTITUTE(C6," days)",""),"(","( 00000")),6))
With FIND:
=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-6)
Without using FIND (Array Formula -CSE):
=MID(A1,MATCH(40,CODE(MID(A1,ROW($1:$25),1)),0)+1,LEN(A1)-6-MATCH(40,CODE(MID(A1,ROW($1:$25),1)),0))
In case anyone is curious, I'm looking for the left parenthetic, which has a corresponding character code of 40 by creating an array of the character codes of the text string. I'm then finding it by using match in the array instead of using the much easier FIND solution. The 6 is a reference to the length of the string " days)". The array of Row($1:$25) would need to be expanded beyond 25 if the length of the strings extended past 25, but the current string maximum length is 21, so I just kept it shorter.
Slightly shorter version of the FIND formula:
=MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-6)
It occurred to me while writing the array formula that you don't need to find the right parenthetic, it is always in the final position. The LEN doesn't really save much space, but a bit.
=MID(A1,FIND("(",A1)+1,2)
Already posted the same thing, see second comment 😉
PS @others: I think the challenge always lies in making a SHORT and ELEGANT solution (which works) using a function or in the other categorie: VBA-code...
I see quite a lot of extremely complicated functions (some even only 'sort-of' work or not at all). But there is no challenge there. Anyone could make a function with 1000 subfunctions in it to do the same thing...
=VALUE(MID(A1,FIND("(",A1)+1,FIND("days)",A1)-(FIND("(",A1)+1)))
=SUBSTITUTE(SUBSTITUTE(RIGHT(A1,9)," days)","",1),"(","",1)
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column1",Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, true),{"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Column1.2",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Column1.2.1", "Column1.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", Int64.Type}, {"Column1.2.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1", "Column1.2.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.2.1", "Output"}})
in
#"Renamed Columns"
FYI, that looks long, but it takes about 10 seconds (and not many more clicks) to build in Power Query.
=SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("(",A1)),CHAR(160)&"days)","")+0
Hello;
my soluce :
no FIND, no SEARCH, no Array
but only if end of string is " days)"
=1*(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," days)",""),"(","00000"),6))
=MID(A1,FIND("(",A1,1)+1,2)
My Formula
RIGHT(LEFT(A1,SEARCH("(",A1,1)+2),2)
use flashfill
In Excel 2016
Type 23 then press enter
Type 3 then press enter
Highlight those two cells and the two cells below it.
Click Flash Fill
Done.
No Formulas, hardly any typing. Too easy.
For Excel 2013 & above: FlashFill. Using the data provided I was able to extract the number of days (from any number of rows!) using just three keystrokes and 2 mouse clicks:
In A2 enter "23", Enter. Then Fill -> FlashFill.
N-I-C-E !!!
Instead of 2 mouse clicks, try: CTRL+E.
Just to be safe .. I think it is best practice to enter more than one matching value (although this example will work with only 23 being entered).
=MID(A1,FIND("(",A1)+1,FIND(" days",A1)-FIND("(",A1))
Just use 2 DIGITS FLASH FILL
=VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(A1, " days)", ""), "(", " "), 2))
=IF(ISNUMBER(LEFT(RIGHT(A1,8),2)*1),LEFT(RIGHT(A1,8),2),RIGHT(LEFT(RIGHT(A1,8),2),1))
=--TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",99)),"
sorry
=0+TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",99))," ",REPT(" ",99)),99*2),99))
& Another one
=-(TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99*2),99))&")")
Hi Chandoo...
You already answer it....
http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/
-Shaj
Hi Shaj...
Thanks for sharing the link. As you can see, this example will fail when you apply the techniques in the Extract numbers from text post as we have 2 numbers in text in some cases.
=VALUE(MID(A3,FIND("(",A3,1)+1,FIND(" ",A3,FIND("(",A3,1))-FIND("(",A3,1)-1)) this works for any digit number
This works as well
--SUBSTITUTE(RIGHT(SUBSTITUTE(A1," days)",""),2),"(","")
=mid(A1, find("(",A1)+1, len(A1)-find("(",A1)-5)
Another VBA formula using Regular Expressions :
Function JustNumberofDays(rng As Range) As integer
Dim Regex As Object
Dim Temp As Variant
Dim strPattern As String
Set Regex = CreateObject("vbscript.regexp")
strPattern = "\d+"
With Regex
.pattern = strPattern
.Global = True
Set Temp = .Execute(rng.Value)
End With
JustNumberofDays = Temp.Item(1).Value
End Function
Hi,
my solution (in French):
=STXT(A1;TROUVE("(";A1)+1;TROUVE(" days";A1)-TROUVE("(";A1)-1)
in English, I think is: =MID(A1,FIND("(",A1)+1,FIND(" days",A1)-FIND("(",A1)-1)
Have a nice day 🙂
=MID(SUBSTITUTE(A1," days)",""),FIND("(",A1,1)+1,100)*1
=VALUE(MID(SUBSTITUTE(A1," days)",""),FIND("(",SUBSTITUTE(A1," days",""))+1,2))
=SUBSTITUTE(REPLACE(A1,1,SEARCH("(",A1),""),CHAR(160)&"days)","")+0
=IF(LEFT(RIGHT(A2,8),1)="(",LEFT(RIGHT(A2,7),2),LEFT(RIGHT(A2,8),2))
=MID(A1,FIND("(",A1)+1,FIND(" ",A1,FIND("(",A1))-(FIND("(",A1)+1))
This is probably just a fix, but here you go:
=LEFT(RIGHT(A1,LEN(A1)-FIND("(",A1)),FIND("days",RIGHT(A1,LEN(A1)-FIND("(",A1)))-1)
=MID(A1,FIND("(",A1)+1,FIND(" days",A1)-FIND("(",A1)-1)
Use simple formula =+LEFT(RIGHT(A1,8),3) , will serve the purpose.
*A1= Nov15 PUTS (23 days)
Only if you assume that the number will always be a two digit number. And because of that, no need for grabbing the extra character after the number (the space).
When you also replace the "(" with "", it will work for numbers lower than 10 too (and even with the special case "(1 day)").
=+SUBSTITUTE(LEFT(RIGHT(A1,8),3),"(","")
Of course, a max of 99 is still assumed in that case.
=LEFT(RIGHT(A1,LEN(A1)-FIND("(",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("(",A1)))-1)
MID(A1,(FIND("(",A1,1)+1),2)
Well here is my formula attempt:
=LEFT(SUBSTITUTE(A1;LEFT(A1;FIND("(";A1));"");FIND(" ";SUBSTITUTE(A1;LEFT(A1;FIND("(";A1));""))-1)
=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-LEN(" days)"))
hi. i'm from Portugal, so here is my solution:
the 6 that you see at the formula, is the number os caracter to the right and is assuming that the text is always: " days)"
=SE(É.ERRO(VALOR(DIREITA(SEG.TEXTO(A1;1;NÚM.CARACT(A1)-6);2)));DIREITA(SEG.TEXTO(A1;1;NÚM.CARACT(A1)-6);1);DIREITA(SEG.TEXTO(A1;1;NÚM.CARACT(A1)-6);2))
=SUSTITUIR( EXTRAE(A1; LARGO(A1)-7; 1) & EXTRAE(A1; LARGO(A1)-6; 1); "("; "")
=SUBSTITUTE( MID(A1; LEN(A1)-7; 1) & MID(A1; LEN(A1)-6; 1); "("; "")
Sub extract()
Dim i As Integer
For i = 1 To 4
Cells(i, 2) = Mid(Cells(i, 1), InStr(1, Cells(i, 1), "(") + 1, 2)
Next i
End Sub
Sub extract()
Dim i As Integer
For i = 1 To 4
Cells(i, 2) = Mid(Cells(i, 1), InStr(1, Cells(i, 1), "(") + 1, 2)
Next i
End Sub
=IFERROR(VALUE(MID(D2,LEN(D2)-7,2)),VALUE(MID(D2,LEN(D2)-6,1)))
Here is one without FIND/SEARCH that is non-array and works for any number of digits. It also does not use hard-coded integers or counters -- as in no expectation of a fixed left/right/mid position and no ROW(1:X) limiting the number of possible characters. The VALUE function even handles the possibility of trimming some extra spaces for something like Nov15 PUTS ( 23 days) while ensuring the result is numeric.
=VALUE(SUBSTITUTE(REPLACE(A1,1,MATCH("(",INDEX(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),0),0),""),"days)",""))
Range("A1:A4").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Replace What:=" days*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="*(", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Hi Chandoo, thanks for the good challenge.
I found very nice solutions, most users have used FIND/SEARCH 🙁
Here is my entry:
=-SUBSTITUTE(RIGHT(A1,9)," days","")
Adding to what I posted above...
there is a CHAR(160) before "days"
So here the revised one:
=-SUBSTITUTE(RIGHT(A1,9),CHAR(160)&"days","")
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,8),"days",""),")",""),"(",""))
=SUBSTITUTE(MID(A1,(SEARCH("(",A1))+1,LEN(A1))," days)","")
LEFT((TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",LEN(A1))),LEN(A1)))),2)
Assume formulae is in A2
=MID(A1,(FIND("(",A1,1)+1),FIND(" ",A1,(FIND("(",A1,1)+1))-FIND("(",A1,1))
Not the most elegant but here it is:
=IFERROR(MID(RIGHT(F4,8),1,2)*1,MID(RIGHT(F4,7),1,1)*1)
=IF(LEFT(MID(A1;((LEN(A1)-7));2))="(";MID(A1;((LEN(A1)-6));2);MID(A1;((LEN(A1)-7));2))
=-LOOKUP(,-MID(A1,FIND("(",(A1))+1,{1,2,3,4,5}))
work 1 - 99999 days
=DIREITA(ESQUERDA(A1;LOCALIZAR("days";A1;1)-1);LOCALIZAR("days";A1;1)-LOCALIZAR("(";A1;1)-1)
=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,8),")",""),"(",""),CHAR(160)&"days",""))+0
In Text to columns , separation basis is as space.
=MID(A1,FIND("(",A1)+1,2)
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2," days)",""),"(",""),"2"))
I hv one doubt
I need to separate 10 digits number in a sentence
For ex
Payment made to Mr A against the invoice no 6548732578 of Rs 36,000/-
I need to separate dat invoice no
Help me
@Srinivas
Can you please ask the question in the Chandoo.org Forums
Please attach a sample file with several sample lines