Extracting numerical content from alpha/numerical string****Resolved

[JFK]

I need to extract the numerical content of an alpha numerical string and find the highest value.

The strings are either 6 or 7 characters in length with the first five characters always being alpha e.g(elect1 or elect15).

I've tried using Val(fieldname) but this doesn't work it returns a zero everytime even though the numeric content of the string is between 1 and 99.

I've also tried to use the CdBl function but this returns an error all the time.

Is there an easy way to do this?

thanks

JFK

[si_the_geek]

use mid to extract the numeric part, then val to convert it to a number.

'This gets the 6th char onwards:
MsgBox Mid(fieldname,6)
 
'Converted to number:
MsgBox Val(Mid(fieldname,6))

[JFK]

Si

Val(Mid(fieldname,6))

That works great except when sorting to find the highest number it seems to be treating it as a string still as 8 appears lower in the list than say 55(when ordering by this).

I want to find the highest number so I can use the next one ie if 55 is the highest used todate then i will use 55 + 1 but at the moment number 9 appears as the highest instead of 55 !

any ideas

JFK

It's ok sorted it thanks for your help