how do I remove zone punches from numeric fields

[Lou Nugai]

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.

 

[Pete_UK]

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

 

[Stefi]

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: