If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how do I remove zone punches from numeric fields
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. |
#2
|
|||
|
|||
how do I remove zone punches from numeric fields
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: 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. |
#3
|
|||
|
|||
how do I remove zone punches from numeric fields
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: 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. |
Thread Tools | |
Display Modes | |
|
|