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

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

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

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