Deleting leading 0s from a character field

Brian Element

Forums

Someone had a question on how to delete leading 0s from a character field such as having the following info:

0123345 3476534 2343255 0023478

If the character contains only numeric information then you can change the field to numeric through the field manipulation.  You probably want to look through it after to make sure there are no errors which could indicate that there is also character data.

If you want to keep the original field you can append a new numeric field and use the @val function on the other field.

If it contains both numeric and alpha numeric characters you could create a custom function to get rid of the leading 0 or does anyone have a way of doing it using the IDEA functions?

 

CB Fri, 10/19/2012 - 06:38

Sub Main
Dim sText As String
Dim sTextNew As String

stext = "01234004"
sTextNew = iReplace(iMid(stext,1,1), "0", "") + iMid(stext,2,iLen(stext))

MsgBox "sText: " & sText & Chr(13) & Chr(13) & "sTextNew: " & sTextNew ,0,"Result"

End Sub

cheers,
chris

Brian Element Fri, 10/19/2012 - 07:45

Hi Chris,

Thanks for the comment. Just wondering for the iReplace, iMid and iLen functions is the i for ideascript specific?  I know in VB there  is the mid and len and replace, both the mid and len work in ideascript but the replace doesn't work, it will give you an error, also the mid and len show up in gold as key words in IDEAScript.  It is nice to know that there is an equivalent for the replace that does work, thanks for that information.

For your script couldn't you have done the same thing without the replace function, just using the mid function?

Thanks

Brian

CB Mon, 10/22/2012 - 00:55

hi brian,

the "i"-prefix is indeed for the use in ideascript.. if you use the idea-filter option you use the "@"-prefix.
i use idea version 8.5 from the german distributor audicon on my job and with
the i-prefix the replace function works also in ideascript. does it not work on your idea version? maybe
there are some differences in the distributed software...

the mid function only cut of things and do not replace the leading 0.. so i have no idea at the moment
how to use the mid instead of replace...

cheers,
chris

Brian Element Mon, 10/22/2012 - 05:37

Hi Chris,

Thanks for the refresher on the i, it has been so long since I have used it I had completely forgotten that you could use the @ functions in the script, I guess the memory is going.  Now that you mention it, it comes back to me that you can do that.

The ireplace works in my version also, it is the visual basic equavalent that doesn't work, so that is where I was getting confused.

The basic difference between the mid and the replace is that the replace changes characters within a string and the mid extracts certain characters from a string at a certain spot.

Thanks

Brian

Steven Luciani Mon, 10/22/2012 - 08:44

I'd like to add something to this discussion. I am pasting an equation I use when I come across this problem. In my experience customer id's are a mix of numeric and character fields like 0003456 and pac123. I link my customer ID fields from a billing file to the customer master file, but in some cases leading zeros are present in one file but not both.@if(@left(PAYER1,1)=@RegExpr(PAYER1,"[A-z]"),PAYER1,@Mid(PAYER1,@FindOneOf(PAYER1,"123456789"),10)) In this equaiton I look at the first character in the payer1 field and if it is a letter, capital or lower case, it will return the original entry in the payer1 field. If it isn't a letter, it goes to the middle of the field where it finds a number from 1-9 and picks up the next 10 numbers. This makes at mid dynamic and accounts for any number of leading zeroes. This equation assumes that the maximum length of the payer1 field is 10. 

Brian Element Mon, 10/22/2012 - 12:48

Hi Steve,

That is a nice equation, that is one thing I don't use enough is the @RegExpr as it is quite powerful but not the easiest function to get your head around.  The only problem with your equation is what happens if after the leading 0s the first character is not a number.  So you have "00ABSCD123" you wouldn't get the the characters.  I tried to figure it out using the equation editor and I couldn't come up with something, so finally I cheated and created a custom function that will perform this.  I have posted it in the custom function forum.

Steven Luciani Thu, 10/25/2012 - 23:18

Hey Brian, 
Thanks for the custom function. I've updated my equation to account for a 00ABC123 scenario (doesn't matter how many zeroes are present).
@if(@left(COL1,1)="0",@mid(COL1,@FindOneOf(COL1,@RegExpr(COL1,"[A-z]|[1-9]")),10),COL1). I'm providing Momoyo a document I call Making @Left, @mid, or @right dynamic versus static functions. I produced it just for situations like this. I think she will make it an appendix to the introductory training for students to work on if they are finished the regular exercises. 
I use RegExpr in IDEA as often as I can and use it even more in VEDIT, a text editor I use to clean up messy text files. A good book to read is 
Sams Teach Yourself Regular Expressions in 10 Minutes. You can probably get it from a library branch. That's where I found it. It's also on sale at Amazon. Cheers

Brian Element Fri, 10/26/2012 - 06:49

Hi Steve, thanks for the update.  Ever thought of setting up a challenge section on the site so we can try your little puzzles?
I also just ordered the Regular Expression book, I found it on Aamzon for $12, should be good.

Steven Luciani Tue, 10/30/2012 - 13:44

Hi Brian,
I'd love to setup an IDEA Equation challenge section, assuming we could provide a small Excel or text file with a few records. People could download the file, test their equations in their version of IDEA then post their equations. We could leave the challenge up for a few days then post our suggested solution.
I find most students and new users of the software are scared of the equaiton editor, this would be a good section to help them get over those fears. If it's possible, I could help you, or administer this part of the site on my own, whichever you prefer.