Count Number of Characters Before the first Letter

[DaveT99]

Can anyone help, I have spent 10mins googling this without Success
How to count the number of characters (numbers) before the first letter, e.g. 7220BECBY the answer would be 4
Many Thanks

[CJ_London]

try

len(val(myfield))

[DaveT99]

Many thanks, got there after a couple of tries...........and a new function learnt
Regards
Dave

[DaveT99]

Oops
if the format is 2201E-2RS1TN9
The above function is counting 5, ideally want it to stop when it reaches the E, i.e count of 4 numeric characters before the 1st Letter or Non Numeric Characyer
Dave

[davegri]

Very odd. The Val function is evaluating the string as scientific notation; 2201 to the minus 2 power which is 22.01 and has length of 5!
Below works without the Val function.

Function tst(arg As String)
    Dim i As Integer
    tst=0    
    For i = 1 To Len(arg)
        If IsNumeric(Mid(arg, i, 1)) Then
            tst = tst + 1
        Else
            Exit Function
        End If
    Next i
End Function

[DaveT99]

Hi Davegri
Thanks for the FUNCTION. Do I insert in the query field build???
Cheers
Dave

[davegri]

Put the function in a module.
Query design view shows how to use it in the query to get size of Fld1.
You can use any string you want instead of Size.

[CJ_London]

2201E-2 is scientific notation for 22.01 so will be interpreted as a number. Your example did not include a - so I did not think of it

It is the E that gives it away. It may be your codes have other 'variations' so I would do a pass through your data to see if there are any other combinations that result in a misread

removing the - won't solve it because 2201E2 is scientific notation for 220100.

instead remove the dp

len(replace(val(myfield),".",""))

or change the E to another character

len(val(replace(myfield,"E","X")))

[DaveT99]

Many thanks both of you..you clever and helpful people.
The format is from a SKF bearing list
Regards
Dave