how do I remove zone punches from numeric fields Lou Nugai 27.09.2006 16:01:02 I have a customer who sends a proprietary file to me at Timex Corp. The data is from an old system. When loaded into excel, there is an alpha code or a special character in the low order units position of the fields. This indicates whether the amount is positive or negative. For instance, 0006688B is the value sent. The "B" is a numeric field with an over punch. How do I tell excel to disregard the over punch so that the "B" converts to a valid numeric digit? Thanks for your assistance with this question. Regards, Lou Nugai Sr. EDI Analyst Timex Corporation.

Re: how do I remove zone punches from numeric fields "Pete_UK" <pashurst[ at ]auditel.net> 27.09.2006 20:57:04 What do you mean by "over punch"? Do you want to disregard the "B" completely, or does it represent another value that you want to appear in another column, or is the value to be added on to the rest of the number ?? Please explain a bit more what it is you want to achieve. Pete Lou Nugai wrote: [Quoted Text] > I have a customer who sends a proprietary file to me at Timex Corp. The data > is from an old system. When loaded into excel, there is an alpha code or a > special character in the low order units position of the fields. This > indicates whether the amount is positive or negative. For instance, 0006688B > is the value sent. The "B" is a numeric field with an over punch. > > How do I tell excel to disregard the over punch so that the "B" converts to > a valid numeric digit? > > Thanks for your assistance with this question. > > Regards, Lou Nugai > Sr. EDI Analyst > Timex Corporation.

RE: how do I remove zone punches from numeric fields Stefi 28.09.2006 09:10:02 If the imported value is in A2 then =VALUE(LEFT(A2,LEN(A2)-1))*IF(RIGHT(A2)="B",-1,1) but you first have to find out which character means negative number. In my example I supposed it to be "B". Regards, Stefi „Lou Nugai” ezt írta: [Quoted Text] > I have a customer who sends a proprietary file to me at Timex Corp. The data > is from an old system. When loaded into excel, there is an alpha code or a > special character in the low order units position of the fields. This > indicates whether the amount is positive or negative. For instance, 0006688B > is the value sent. The "B" is a numeric field with an over punch. > > How do I tell excel to disregard the over punch so that the "B" converts to > a valid numeric digit? > > Thanks for your assistance with this question. > > Regards, Lou Nugai > Sr. EDI Analyst > Timex Corporation.