Query to return number before letters

[Mark0123]

Hi all,

I would like to run a query which takes a code which contains a mixture of number and letter and returns all the number before the first letter. I was using the left function (=Left(([Codes],2)) but sometimes there is one number sometime two. The desired results are shown below;

7pol2try36 = 7
12cet9fre55 = 12
10yea3gtr77 = 10

Is this possible?

Thank you in advance.
Mark

 

[pr2-eugin]

Try the Val Function.

? Val("7pol2try36")
 7 
? Val("12cet9fre55")
 12 
? Val("10yea3gtr77")
 10

 

[Mark0123]

Thank you

It works until it encounters an E followed by a number in the code. It then adds zeros depending on the number after the E. For example;

10E3 = 10000
12E1 = 120
9E5 = 900000

Any thoughts?

 

[Mark0123]

I added the replace function. So problem solved. Thank you all

Mark