Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with MID()
I am trying to use MID to extract teh first 2 numbers of a 4 digit number
that is in a cell. The problem is, the number sometimes has a "0" as the first digit and when this happens, MID() ignores the 0, and reads the 2nd and 3rd character. So: Cell A1 = 0123 Cell B2 =MID(A1,1,2) Returns 12 instead of 1 How can I fix this? I need tehm to stay numbers so I can use them in mathmatical operations in another formula. I thought I had teh problem solved by formatting the cell to "Number -custom 0000" TIA Adam |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with MID()
One way
=TEXT(MID(A1,1,LEN(A1)-2),"00") or =MID(A1,1,LEN(A1)-2) and use custom format -- Regards, Peo Sjoblom (No private emails please) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... I am trying to use MID to extract teh first 2 numbers of a 4 digit number that is in a cell. The problem is, the number sometimes has a "0" as the first digit and when this happens, MID() ignores the 0, and reads the 2nd and 3rd character. So: Cell A1 = 0123 Cell B2 =MID(A1,1,2) Returns 12 instead of 1 How can I fix this? I need tehm to stay numbers so I can use them in mathmatical operations in another formula. I thought I had teh problem solved by formatting the cell to "Number -custom 0000" TIA Adam |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with MID()
Well, that reduced the error somewhat, but not compeltely: The full formula
being used is: =IF(AND(ISTEXT(A4),ISTEXT(D4)),(MAX(ABS(MID($AF$2, 1,LEN($AF$2)-2) - MID(AF4,1,LEN(AF4)-2)),ABS(MID(AF4,3,LEN($AF4)-2) - MID(AF4,3,LEN($AF4)-2)))+MAX(0, MIN(ABS(MID($AF$2,1,LEN($AF$2)-2) - MID(AF4,1,LEN($AF4)-2))),ABS((MID(AF4,3,LEN($AF4)-2) - MID(AF4,3,LEN($AF4)-2)))-ROUND(MAX(ABS(MID($AF$2,1,LEN($AF$2)-2) - MID(AF4,1,LEN($AF4)-2)),ABS(MID(AF4,3,LEN($AF$2)-2) - MID(AF4,3,LEN($AF4)-2)))/2,0))),"") AF2 = 0703 AF4 = 1205 The ISTEXT() checks are "TRUE" The answer should be 4 I know the math part of the formula is correct. The error has to be somewhere in the MID() extractions. "Peo Sjoblom" wrote in message ... One way =TEXT(MID(A1,1,LEN(A1)-2),"00") or =MID(A1,1,LEN(A1)-2) and use custom format -- Regards, Peo Sjoblom (No private emails please) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... I am trying to use MID to extract teh first 2 numbers of a 4 digit number that is in a cell. The problem is, the number sometimes has a "0" as the first digit and when this happens, MID() ignores the 0, and reads the 2nd and 3rd character. So: Cell A1 = 0123 Cell B2 =MID(A1,1,2) Returns 12 instead of 1 How can I fix this? I need tehm to stay numbers so I can use them in mathmatical operations in another formula. I thought I had teh problem solved by formatting the cell to "Number -custom 0000" TIA Adam |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with MID()
On Sun, 27 Nov 2005 17:33:18 -0600, "Adam Kroger"
wrote: I am trying to use MID to extract teh first 2 numbers of a 4 digit number that is in a cell. The problem is, the number sometimes has a "0" as the first digit and when this happens, MID() ignores the 0, and reads the 2nd and 3rd character. So: Cell A1 = 0123 Cell B2 =MID(A1,1,2) Returns 12 instead of 1 How can I fix this? I need tehm to stay numbers so I can use them in mathmatical operations in another formula. I thought I had teh problem solved by formatting the cell to "Number -custom 0000" TIA Adam =MID(TEXT(A1,"0000"),1,2) or =INT(A1/100) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |