Convert Text to Number in a Query

[David Ball]

Hi,

I have a Text field, called KPI, of the format ABC1, ADC4, ABC15, etc.

I have set up a field in the query to extra the "number" part, but when I try to sort, it sorts with all items starting with 1 first, so 15 comes before 2, etc.

The formula I use to extract the numbers is:

SortNum: Right([KPI],Len([KPI])-3)

Is there a way to convert this new field to a Number format?

Thanks very much

Dave

 

[CJ_London]

use val to convert text to number - val("123A") will convert to 123

so

SortNum: val(Right([KPI],Len([KPI])-3))

 

[pbaldy]

Also, if there are always 3 letters, you can use the Mid() function rather than the combined Right() and Len() functions.

 

[David Ball]

Thanks very much, both very helpful