Frank Kabel wrote...
have you tried the UDF?. It should return only this version
number. At least it worked for your example data?
...

*YOU* didn't test your UDF on the filenames

ABC_DEF123_IJK_V2.xls

or

ABC DEF123 IJK V7.xls

for which your UDF returns 0. Why? Classic off-by-one indexing error.
The statement

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

would need to be replaced with

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

in order to pick up version numbers immediately preceding '.xls', but
that exposes another failing of your UDF. If the change above were
made, then for the filenames

ABC_DEF123_IJK_V5.0.xls

and

ABC_DEF123_IJK_6_0.xls

it returns 50 and 60, respectively, because you're using a character at
a time algorithm that's clueless about '.' being a valid numeric
character as long as it appears once with at least one adjacent
numeral. Even then it'd choke on '6_0', which appears to be a valid
alternative to 6.0.

Anyway, a UDF that copes with all the OP's variations given so far
needs to locate the rightmost numeric substring, and that requires at
least two loops - first to find the end of the rightmost numeric
substring, then to find it's beginning. Here's an alternative UDF
that's more permissive with its argument and returns a string rather
than a number, so strings without such numeric substrings return ""
rather than #VALUE!.


Public Function vn(ByVal v As Variant) As String
Dim i As Long, n As Long, s As String, t As String

n = Len(v) - 4

If n = 0 Then Exit Function 'v is too short


For i = 0 To 4

If Mid(v, n - i, 1) Like "[0-9]" Then
v = Mid(v, n - 4, 5 - i)
n = 5 - i
Exit For
End If

Next i


If i 4 Then Exit Function


For i = n - 1 To 0 Step -1
s = Right(v, n - i)
t = Application.WorksheetFunction.Substitute(s, "_", ".")

If IsNumeric(s) Then
vn = s

ElseIf IsNumeric(t) Then
vn = t

Else
Exit For

End If

Next i


End Function


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