Hi
in the UDF change the line
sValue = Mid(rng.Value, Len(rng.Value) - 9, 5)

to
sValue = Mid(rng.Value, Len(rng.Value) - 8, 5)

This solves your second example. The first example is difficult as you
said that the version number is within the last 5 characters before
'.xls'. But in this example there's also a part of a previous number
within this range. To solve also this problem try the following UDF:
Public Function Get_Numerics(rng As Range) As Double
Dim i As Integer
Dim res
Dim sValue
Dim version
version = False
sValue = Mid(rng.Value, Len(rng.Value) - 8, 5)
For i = Len(sValue) To 1 Step -1
If IsNumeric(Mid(sValue, i, 1)) Then
version = i
res = Mid(sValue, version, 1)
While IsNumeric("0" & Mid(sValue, version - 1, 1) & res)
version = version - 1
res = Mid(sValue, version, 1) & res
Wend
Exit For
End If
Next i
res = CDbl(res)
Get_Numerics = res
End Function

Not fully tested but give it a try


--
Regards
Frank Kabel
Frankfurt, Germany


frank,

i had tried your udf.

here are two strings that i tested it for, wherein it failed.

ABC_DEF_IJK_MNO070_UTHI05_1.1_V1.xls

the udf returned 11, the expected output is 1.

ABC_DEF_IJK_MNO120_QTLY19BREP_1.xls

the udf returned 0, the expected output is 1.

there are other cases wherein it gave the correct output.

thank you for your response and help.

mac.


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