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

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

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

VB Code:

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

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