# Can you extract numbers from text – homework

Posted on October 30th, 2015 in Excel Challenges , Learn Excel - 112 comments

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```

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.

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.

 Save a range as text file using VBA [tutorial] Employee training tracker & calendar – tutorial & download
 Written by Chandoo Tags: find, homework, Learn Excel, Microsoft Excel Formulas, search, text processing Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 112 Responses to “Can you extract numbers from text – homework”

1. Michael (Micky) Avidan says:

=SUBSTITUTE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)," days",)

• Uday says:

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

• Peter says:

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

• XLarium says:

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)),""))

• XLarium says:

The last formula is an array formula, of course.

• Deepak says:

Cool trick buddy!!

• PAUL says:

Very cool! Congratulations!

• Chandoo says:

Awesome use of - and ()s. Thanks for sharing it.

• torkunc says:

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.

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!

3. MF says:

My formula attempt:
=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),20)),"(","")," days)","")

4. Abhay says:

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.

• Teh says:

Exactly. Simple and straight to the point!

5. Abhay says:

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

• Michael (Micky) Avidan says:

TWO "minus signs" will be sufficient (instead of VALUE)

6. Dheeraj says:

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.

7. Aniket says:

=IF(ISNUMBER(VALUE(MID(A1,LEN(A1)-7,2))),VALUE(MID(A1,LEN(A1)-7,2)),VALUE(MID(A1,LEN(A1)-6,2)))

8. Michael (Micky) Avidan says:

Another approach: =MID(A1,FIND("(",A1)+1,FIND(" days)",A1)-FIND("(",A1)-1)

9. Andi says:

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)%,""))

10. milang says:

=VALUE( MID( [@Data]; SEARCH( " ("; [@Data]) + 2; SEARCH( " days)"; [@Data]) - SEARCH( " ("; [@Data]) - 2))

11. Villalobos says:

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

12. Crisu says:

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.

13. Saurabh says:

=VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(Text," days)",),"(",),2))

14. Olvier says:

=IF(LEFT(RIGHT(A1,8),1)="(",VALUE(LEFT(RIGHT(A1,7),1)),VALUE(LEFT(RIGHT(A1,8),2))

15. April Du says:

Function getDays(myCell As Range)

n = InStr(myCell.Value, "(")
m = InStrRev(myCell.Value, " ")
getDays = Mid(myCell.Value, n + 1, m - n - 1)

End Function

16. Jojek says:

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)

17. JeanMarc says:

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

• jomili says:

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.

😉

18. Chandeep Chhabra says:

=VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,RIGHT(A1,6),""),"(",""),2))

19. Harry says:

Hilariously 90% of these formulas are completely wrong.

20. Harry says:

=LEFT(REPLACE(A1,1,FIND("(",A1,1),""),2)

That works 100% of the time.

• jason m says:

Not if the number of days is greater than 99.

21. Jason says:

=LEFT(RIGHT(A1,(LEN(A1)-FIND("(",A1))),LEN(RIGHT(A1,(LEN(A1)-FIND("(",A1))))-6)

22. Mike H says:

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 :¬)

23. Sachidanand Singh says:

=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

24. Ira says:

=""&(0+RIGHT((SUBSTITUTE(SUBSTITUTE(C6," days)",""),"(","( 00000")),6))

25. Michael Pennington says:

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.

• Michael Pennington says:

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.

26. Samtheman says:

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

27. Maria G says:

=VALUE(MID(A1,FIND("(",A1)+1,FIND("days)",A1)-(FIND("(",A1)+1)))

28. Ben says:

=SUBSTITUTE(SUBSTITUTE(RIGHT(A1,9)," days)","",1),"(","",1)

29. Ken Puls says:

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"

• Ken Puls says:

FYI, that looks long, but it takes about 10 seconds (and not many more clicks) to build in Power Query.

30. jason m says:

=SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("(",A1)),CHAR(160)&"days)","")+0

31. modeste says:

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

32. Abhishek Jain says:

=MID(A1,FIND("(",A1,1)+1,2)

My Formula

RIGHT(LEFT(A1,SEARCH("(",A1,1)+2),2)

use flashfill

35. Bernard says:

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.

36. SamCal says:

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.

• Michael (Micky) Avidan says:

N-I-C-E !!!
Instead of 2 mouse clicks, try: CTRL+E.

• Bernard says:

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

37. KM Zachariah says:

=MID(A1,FIND("(",A1)+1,FIND(" days",A1)-FIND("(",A1))

Just use 2 DIGITS FLASH FILL

39. Jeff Koenig says:

=VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(A1, " days)", ""), "(", " "), 2))

40. eln lafrenier says:

=IF(ISNUMBER(LEFT(RIGHT(A1,8),2)*1),LEFT(RIGHT(A1,8),2),RIGHT(LEFT(RIGHT(A1,8),2),1))

41. Deepak says:

=--TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",99)),"

• Deepak says:

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))&")")

42. Shaj says:

Hi Chandoo...

http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

-Shaj

• Chandoo says:

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.

• Chandra Mohan says:

=VALUE(MID(A3,FIND("(",A3,1)+1,FIND(" ",A3,FIND("(",A3,1))-FIND("(",A3,1)-1)) this works for any digit number

43. sam says:

This works as well
--SUBSTITUTE(RIGHT(SUBSTITUTE(A1," days)",""),2),"(","")

44. Sevinj says:

=mid(A1, find("(",A1)+1, len(A1)-find("(",A1)-5)

45. Stephane says:

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

46. Hocine Satour says:

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 🙂

47. Chirayu says:

=MID(SUBSTITUTE(A1," days)",""),FIND("(",A1,1)+1,100)*1

48. Vignesh Sampath Kumar says:

=VALUE(MID(SUBSTITUTE(A1," days)",""),FIND("(",SUBSTITUTE(A1," days",""))+1,2))

49. jason m says:

=SUBSTITUTE(REPLACE(A1,1,SEARCH("(",A1),""),CHAR(160)&"days)","")+0

50. Appaji Naik says:

=IF(LEFT(RIGHT(A2,8),1)="(",LEFT(RIGHT(A2,7),2),LEFT(RIGHT(A2,8),2))

51. Ehtisham Ali says:

=MID(A1,FIND("(",A1)+1,FIND(" ",A1,FIND("(",A1))-(FIND("(",A1)+1))

52. tenzing says:

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)

53. Fred says:

=MID(A1,FIND("(",A1)+1,FIND(" days",A1)-FIND("(",A1)-1)

54. Mukesh says:

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.

55. Sachin Gupta (coolsac12) says:

=LEFT(RIGHT(A1,LEN(A1)-FIND("(",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("(",A1)))-1)

56. Surjendu says:

MID(A1,(FIND("(",A1,1)+1),2)

57. Bhavesh says:

Well here is my formula attempt:

=LEFT(SUBSTITUTE(A1;LEFT(A1;FIND("(";A1));"");FIND(" ";SUBSTITUTE(A1;LEFT(A1;FIND("(";A1));""))-1)

58. Ata says:

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-LEN(" days)"))

59. Sérgio Silva says:

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

60. csar says:

=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); "("; "")

61. Jan says:

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

62. Mokskie says:

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

63. Damodaran says:

=IFERROR(VALUE(MID(D2,LEN(D2)-7,2)),VALUE(MID(D2,LEN(D2)-6,1)))

64. David N says:

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)",""))

65. f(x)dx says:

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

66. Khalid NGO says:

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","")

67. Khalid NGO says:

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","")

68. SYED ALI says:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,8),"days",""),")",""),"(",""))

69. =SUBSTITUTE(MID(A1,(SEARCH("(",A1))+1,LEN(A1))," days)","")

70. Pramod Joshi says:

LEFT((TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",LEN(A1))),LEN(A1)))),2)

71. Andy BW says:

Assume formulae is in A2

=MID(A1,(FIND("(",A1,1)+1),FIND(" ",A1,(FIND("(",A1,1)+1))-FIND("(",A1,1))

72. bVs says:

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

74. MichaelCH says:

=-LOOKUP(,-MID(A1,FIND("(",(A1))+1,{1,2,3,4,5}))
work 1 - 99999 days

75. Daniel says:

=DIREITA(ESQUERDA(A1;LOCALIZAR("days";A1;1)-1);LOCALIZAR("days";A1;1)-LOCALIZAR("(";A1;1)-1)

76. mukesh says:

=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,8),")",""),"(",""),CHAR(160)&"days",""))+0

77. Rony says:

In Text to columns , separation basis is as space.

78. Anitha Reddy says:

=MID(A1,FIND("(",A1)+1,2)

 Save a range as text file using VBA [tutorial] Employee training tracker & calendar – tutorial & download