Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove (form) check-boxes from multiple excel fields? | Excel Discussion (Misc queries) | |||
add option in pivot tables to remove subtotals for fields | Excel Worksheet Functions | |||
Can't Insert of remove worksheet in an existing workbook | Excel Discussion (Misc queries) | |||
removing leading zeros in numeric fields | Excel Discussion (Misc queries) | |||
How to remove Drop Page Fields Here from Pivot Table | Excel Discussion (Misc queries) |