Parse Data

[Lynn_AccessUser]

I have the following sample data:

LON09844
NUOS00101
NU-U00092

I am trying to subtract one from the end number. The following code worked until I realized not all of the number sequences began with 0.

NewField: Left$([BegBates],InStr(1,[BegBates],"0")-1) & Format((Right([BegBates],5)-1),"00000")

As a result, I am getting errors on the following sample fields:

LON19514
LON20937
LON90029

Is there a way to perform the left function to the first number instead of to a particular number/character or length.

For example, return all of the letters on the left up to the first digit. So I would see:

LON09844 LON
NUOS00101 NUO
NU-U00092 NU-U
LON19514 LON
LON20937 LON
LON90029 LON


Thank you!

 

[KenHigg]

I think your best bet on this is going to be to write a custom function...

???
kh

 

[DALeffler]

If the last 5 characters in your field are always numeric characters, use the Len function:

Format((Right([MyField],5)-1),"00000")

for the last 5 numeric characters - 1;

Left([MyField],Len([MyField])-5)

for the charaters before the last five charaters in the string;

[MyField] & " " & Left([MyField],Len([MyField])-5)

for this format:

LON09844 LON
NUOS00101 NUO
NU-U00092 NU-U
LON19514 LON
LON20937 LON
LON90029 LON