finding a value in a string

i am looking for some help in finding a particular value in a string.

i have strings that look like:

ABC_DEF123_IJK_V(1).xls

or

ABC_DEF123_IJK_V2.xls

or

ABC_DEF123_IJK_V3_A.xls

or

ABC_DEF123_IJK_V4_NA.xls

or

ABC_DEF123_IJK_V5.0.xls

or

ABC_DEF123_IJK_6_0.xls

or

ABC DEF123 IJK V7.xls

these are the filenames of various files that users would have created
and made an entry into this one master sheet along a column. i have as
many nomenclatures as i have users.

i need to extract the (1, 2, 3, 4, 5, 6, 7) from the strings. they
denote a certain value that is of importance to other calculations.

now, i have created upto 5 columns with RIGHT, MID, LEFT and WHAT HAVE
YOU and WHAT NOT to extricate the values from the strings. i have over
3000 rows from which to extract data. i have even combined upto 3 IF's
to get the desired output in some cases. only problem is, i have more
variety than the number of IFs allowed (7) incorporating ISNUMBER,
VALUE and ISTEXT functions. actually, after the 3rd IF, the formula has
more number of braces than characters!!! i am not able to create a
single formula that would take care of all the variety i have. over 5-6
columns, and even after that, a little bit of manual tweaking, allows
me to get the right value. however, it is quite cumbersome.

would anyone be able to give me one formula that could pick that number
for me from the string? there is one commonality in those strings. the
number that i require to be picked up resides within 5 places to the
left of the ".xls".

thanks in advance,

mac.


---
Message posted from http://www.ExcelForum.com/