how do I remove zone punches from numeric fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lou Nugai - 27 Sep 2006 17:01 GMT

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.

Reply to this Message

Pete_UK - 27 Sep 2006 21:57 GMT

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

> 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
[quoted text clipped - 10 lines]
> Sr. EDI Analyst
> Timex Corporation.

Reply to this Message

Stefi - 28 Sep 2006 10:10 GMT

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:

Reply to this Message