Jump to content

Stripping leading zeros

This topic is 7809 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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:






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. crazy.gif

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! smirk.gif

Link to comment
Share on other sites


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.


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, "

Link to comment
Share on other sites

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:


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

Link to comment
Share on other sites

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


Link to comment
Share on other sites

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 crazy.gif

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! grin.gif YEP, I can add this to my *addiction* list, for sure!

Link to comment
Share on other sites

This topic is 7809 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.