LaRetta Posted November 26, 2002 Share Posted November 26, 2002 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! Link to comment Share on other sites More sharing options...
andygaunt Posted November 26, 2002 Share Posted November 26, 2002 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, " Link to comment Share on other sites More sharing options...
falkaholic Posted November 26, 2002 Share Posted November 26, 2002 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. Link to comment Share on other sites More sharing options...
LaRetta Posted November 26, 2002 Author Share Posted November 26, 2002 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! Link to comment Share on other sites More sharing options...
spb Posted November 26, 2002 Share Posted November 26, 2002 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 Link to comment Share on other sites More sharing options...
andygaunt Posted November 26, 2002 Share Posted November 26, 2002 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 More sharing options...
LaRetta Posted November 26, 2002 Author Share Posted November 26, 2002 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! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7949 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 accountSign in
Already have an account? Sign in here.
Sign In Now