Hi
change the formula
=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN
(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5))))

to
=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE("_" &
A1,"_",".",LEN("_"&A1)-LEN(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/
5),1+MOD(seq,5))))


--
Regards
Frank Kabel
Frankfurt, Germany


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.

ABC_DEF_IJK_MNO_P04_1.xls = 04.1 (1)

ABC_DEF_IJK_Draft1a.1.xls = .1 (1)

ABC_DEF_TE020_OLM__Draft1B_V1-1.xls = -1 (1)

most of the correct outputs are in the format '1.0' or '2.0'. i have
no issues with these. some also had a leading space, such as ' 1'.

frank's formula:


=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN
(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5))))


ABC_DEF_CV070_ARC_1.0_v1.xls = 0 (1)

ABC_DEF_AP130_1.2_v1.xls = 2 (1)

ABC_DEF_ARC_1.3_V2.xls = 3 (2)

ABC_DEF_MD070_v1.0_V1.xls = 0 (1)

this was a good idea:

ABC_DEF_TE020_CAPS_1_V().xls = #N/A

mac.


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