icestationzbra wrote...
i tried the UDF provided by harlan, as well as the formula
provided by frank. here are a few cases where they did not
return the expected output (which have been provided in
braces). the number after '=' are the actual outputs.
...

See my most recent previous response in which I mentioned the Subst
function. It's beginning to look like it's the only thing that would
work, and you might need to change the formula to

=subst(LEFT(RIGHT(O1,9),5),".*\D[._]?(\d([._]\d+)?).*","$1")

this was a good idea:

ABC_DEF_TE020_CAPS_1_V().xls = #N/A

But you've also written that you have filenames like

ABC_DEF123_IJK_V4_NA.xls

for which 4 is presumably the result you seek. If you want to look for
the first numeric substring to the right of '_V' when present, then the
Subst function could accomodate that, but that begs the question how to
handle

ABC_DEF123_IJK_V5_V.xls

?

There does come a point at which if enough humans are allowed to throw
enough random character variations at a computer, the computer won't be
able to detect any pattern.


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