#1   Report Post  
Posted to microsoft.public.excel.misc
Adam Kroger
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Adam Kroger
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
have some problem with database baldamenti Excel Discussion (Misc queries) 1 October 13th 05 05:38 PM
Urgent Help Required on Excel Macro Problem Sachin Shah Excel Discussion (Misc queries) 1 August 17th 05 06:26 AM
Problem With Reference Update Egon Excel Worksheet Functions 17 July 16th 05 05:45 AM
Copy an Drag cell Formula Problem Nat Excel Discussion (Misc queries) 1 June 20th 05 03:24 PM
Freeze Pane problem in shared workbooks JM Excel Discussion (Misc queries) 1 February 1st 05 01:04 AM


All times are GMT +1. The time now is 01:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"