I have a numeric data field of 2,000 records. This field was an import from an old DOS program and originally was a text field.This field (CPMS#) contains a 'mixed bag' of numbers, such as:

1981

01983

001876

09835

041922

I need to remove all leading zeros from this set. This is on a stand-alone system and I would like to use REPLACE (calc) to run through the records for stripping. I thought of isolating the various *types* (such as finding 01983) and then using something like Right(CPMS#,4) ... repeating with each found set type; however, I think there's an easier way and *finding* keepings bringing up mixed data.

Is there a formula I can use to identify the leading zeros, regardless of the numeric length, and then remove them in one pass? I'll bet there is!

LaRetta,

One way is to do a replace using the substitute function.

*** NOTE ****

Try this first on a copy of the database. Don't do it on the original unless you are completely happy with the results.

*** END HUGE WARNING!!! ***

I have done this one to allow for the data you have included in your posts. It will first remove the set of two zero's and then the individual ones.

Substitute ( Substitute ( your field here, "

You can change the number formating for that field to General Format, but that only shows that way in browse mode.

That is if you want to have it that why, otherwise I think replace() or middle() will be your ticket.

Hey Andy!!! You da MAN!!! That did it exactly right!!! Substitute, huh? I haven't tried anything with that one yet. Thanks for helping -- God, I love this program!

If you need the result to remain a text field, and if there are no alpha characters in the original data, you could do a one-time Replace with calculation, dumping them into a new field, where the calc is something like:

NumToText(TextToNum(YourField))

If you convert to num, leading zeros will vanish, then convert back.

Now you can delete the original field and move your new field into its place.

Steve Brown

LaRetta, you are very welcome.

Substitute can be powerful, nested ones too ( as your's above).

But, as always a government health warning is issued upon suggestion of this item

****************************************

The substitute is used at your own risk.

May cause problems with data integrity if not used properly.

We, the forum cannot be held accountable for its misuse.

Addiction is probable, so caution is advised when using

****************************************

Thanks to all of you for your suggestions on this. This field was established in FM as numeric with auto-enter CPMS# (a different field auto-enters the unique sn#). I had it set up to prohibit modification and had to de-select that. This should have been addressed at the time of data migration ... but I had so many fields to convert and manipulate that this one was missed

So now I re-selected 'do not allow modificaton', it's numeric auto-enter and should run correctly. Substitute within Substitute ... very cool concept, Andy! YEP, I can add this to my *addiction* list, for sure!