Remove Split or Trim Leading Zero's

[Guest]

I have a text field that I need to extract a portion of the information from.

A typical field might look like this.

000000012345678CR04-05-05

My ultimate goal is to return what turns out to be an account number. In
this case 12345678

I need to remove the leading zero's from it and remove anything after the
account number.

The are several problems with the data:
1) The number of leading zero's varies from 0 to 10
2) The CR.... string that follows is sometimes listed as DR.... or just a -.
3) Some of the account number actually are in perfect order with no leading
zero's and no trailing garbage.
4) The account number in question will vary in length from 6 to 15 numbers.

One consant is that IF there is garbage after the account number, is is
always "D*" or "C*" or "-*"

So in summary there are two questions

A) How to remove the leading zeros from a text field.
B) How to remove any garbage follows a particular string.

I would also like to be able to have a macro run this for me as well.

Thanks for everyone's efforts.

Scott

 

[LGC]

If the account number is always 15 digits or less with no alphabetic
characters, you may simply use the following:

=Cstr(Val([NameOfField]))

for a text value, or

Val([NameOfField])

if your account number is numeric.

For example:

Cstr(Val("000000012345678CR04-05-05")) = "12345678"
Val("000000012345678CR04-05-05") = 12345678

Hope this helps.

LGC