icestationzbra wrote...
...
i have strings that look like:

ABC_DEF123_IJK_V(1).xls
...
ABC_DEF123_IJK_V2.xls
...
ABC_DEF123_IJK_V3_A.xls
...
ABC_DEF123_IJK_V4_NA.xls
...
ABC_DEF123_IJK_V5.0.xls
...
ABC_DEF123_IJK_6_0.xls
...
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.
...
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".

If it weren't for the filename

ABC_DEF123_IJK_6_0.xls

you could use the formula

=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(A1,9),5),
6-INT((ROW(INDIRECT("1:25"))-1)/5),
1+MOD(ROW(INDIRECT("1:25"))-1,5))))

to pull the version number from the filename in cell A1. I don't think
there's any compact way to handle underscores between numerals as
decimal points, but I could be wrong.


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