instr first instance of letter

[deekras]

i have received a file that has one field per record. i have to split it into two. i know how to use instr, mid, etc. but this is more complex because the character position of the break is different for each field. the only common thing between all of them is that the first field should only be the numbers and the second should only be the letters. in other words, i split the two fields where the letters begin.

how do i determine the position of the first letter?

 

[dcx693]

Assuming the fields begin with numeric values:

To get the numeric portion, use the Val function like this:
Val([myfield])

To get the text portion, use this:
Mid([myfield],Len(Cstr(Val([myfield])))+1)

The expression
Len(Cstr(Val([myfield])))+1
retrieves the position of first text character

 

[deekras]

thanks!