only numbers from the file name.

[snjpverma]

My filename is like this e.g. abc-253-sffdd.xlsm. I want only the number from the filename as my return value. in the above example I would like my return value to be 253.

In below example.
drvd951sdfdfd.xlsm is my file name. the return value should be 951.

Whatever number is there in the filename is contiguous. There are no noncontinguous numbers. there isn't 9 5 and 1 separately. Hope i am able to explain it clearly.
Please assist on the same.

 

[Macropod]

You could use the following array formula:

=--MID(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1),MIN(IF(ISNUMBER((--MID(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1),ROW(INDIRECT("1:"&LEN(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1)))),1))),(ROW(INDIRECT("1:"&LEN(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1))))),"")),MAX(IF(ISNUMBER((--MID(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1),ROW(INDIRECT("1:"&LEN(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1)))),1))),(ROW(INDIRECT("1:"&LEN(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1))))),""))-MIN(IF(ISNUMBER((--MID(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1),ROW(INDIRECT("1:"&LEN(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1)))),1))),(ROW(INDIRECT("1:"&LEN(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".xls",CELL("filename"))-FIND("[",CELL("filename"))-1))))),""))+1)

Hopefully someone else will post something simpler!

Note: the use of a helper cell could greatly simplify the formula. For example, if you had:

=MID(CELL("filename"),LEN(LEFT(CELL("filename"),FIND("[",CELL("filename"))))+1,LEN(CELL("filename"))-SEARCH(".xls",CELL("filename")))

in A1, the above could be reduced to:

=--MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),"")),MAX(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),""))-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),""))+1)

 

[snjpverma]

thanks for your effort macropod. but your formula gives me #value error. I am using excel 2007, is it due to this I am getting error?

 

[Macropod]

As I said in the post, it is an array formula. Did you use Ctrl-Shift-Enter when you input it?

 

[snjpverma]

Thanks a lot expert. It works great. I too hope if someone comes up with a simpler solution.

 

[István Hirsch]

Does the number to extract always have three digits?

 

[Rick Rothstein]

Thanks a lot expert. It works great. I too hope if someone comes up with a simpler solution.

Here is a shorter, normally entered formula for you to try...

=-LOOKUP(0,-LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),9),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

Note: The above formula assumes your number will consist of less than 10 digits and that the number you want returned is the only number in the text.

 

[Macropod]

That's much better though, for the OP's purposes, it would need to be:

=LOOKUP(0,-LEFT(MID(CELL("filename"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},CELL("filename")&"0123456789")),9),ROW(INDEX(A:A,1):INDEX(A:A,LEN(CELL("filename"))))))

 

[Rick Rothstein]

Here is a shorter, normally entered formula for you to try...

=-LOOKUP(0,-LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),9),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

Note: The above formula assumes your number will consist of less than 10 digits and that the number you want returned is the only number in the text.

Actually, in order for the above formula to be guaranteed to work all the time, the number you want to return must be 3 or more digits long. Can you tell us if that will be the case?

 

[Rick Rothstein]

That's much better though, for the OP's purposes, it would need to be:

=LOOKUP(0,-LEFT(MID(CELL("filename"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},CELL("filename")&"0123456789")),9),ROW(INDEX(A:A,1):INDEX(A:A,LEN(CELL("filename"))))))

Oops... I misread the question as to where the filename came from. Thanks for fixing the formula... however, there is a proviso about the formula that I posted in 7. For an example of the problem, try the formula on this file name... drvd12novsdfdfd.xlsm