2 DIGITS OUT OF 4
 

From a four digit number I want to have a formula that extracts only the
middle two numbers eg." 5062" would equal "06" or "4113" would equal "11".

GK

=--MID(A1,2,2)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"gregork" wrote in message
...
From a four digit number I want to have a formula that extracts only the
middle two numbers eg." 5062" would equal "06" or "4113" would equal "11".

GK

On Sun, 20 Feb 2005 04:23:12 +1300, "gregork"
wrote in microsoft.public.excel.misc:

From a four digit number I want to have a formula that extracts only the
middle two numbers eg." 5062" would equal "06" or "4113" would equal "11".

Perhaps I'm missing something, but doesn't
=MID(A1,2,2)
do that?

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

The -- bit assumed you wanted numbers as stated, though you wouldn't get 06
which you also stated. If you really want a string returned then just skip
the -- bit.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" wrote in message
...
=--MID(A1,2,2)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--

"gregork" wrote in message
...
From a four digit number I want to have a formula that extracts only the
middle two numbers eg." 5062" would equal "06" or "4113" would equal
"11".

GK

MID is a text function, so just for the fun of it a pure numeric approach
too:

=INT(MOD(A1,1000)/10)

Best wishes Harald


"gregork" skrev i melding
...
From a four digit number I want to have a formula that extracts only the
middle two numbers eg." 5062" would equal "06" or "4113" would equal "11".

GK

Thanks for all the replies...they all do what I wanted cheers.

GK

"Harald Staff" wrote in message
...
MID is a text function, so just for the fun of it a pure numeric approach
too:

=INT(MOD(A1,1000)/10)

Best wishes Harald


"gregork" skrev i melding
...
From a four digit number I want to have a formula that extracts only the
middle two numbers eg." 5062" would equal "06" or "4113" would equal
"11".

GK

On Sat, 19 Feb 2005 22:50:27 +0100, "Harald Staff"
wrote in microsoft.public.excel.misc:

MID is a text function, so just for the fun of it a pure numeric approach
too:

=INT(MOD(A1,1000)/10)

That MID() is a text function doesn't seem to matter - TEXT() seems
implied. To wit: =MID(5062,2,2) gives 06 which is what the OP wanted.

Your function omits the leading zero.

"gregork" skrev i melding
...
From a four digit number I want to have a formula that extracts only the
middle two numbers eg." 5062" would equal "06" or "4113" would equal "11".

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

"Michael Bednarek" skrev i melding
...

That MID() is a text function doesn't seem to matter - TEXT() seems
implied. To wit: =MID(5062,2,2) gives 06 which is what the OP wanted.

Relax man. Solutions posted here are also archived and used by readers which
may have slightly different needs, so alternate approaches to a problem is
always good and nothing to get worked up about. I know your solution works
fine and that it probably solved the initial problem. Just wanted to say
that a text approach to number material is not the only way to go. Text
functions are e.g. nothing but trouble when we calculate date and time..

Your function omits the leading zero.

As we both know, 6 and 06 is the same number. Custom number format 00 will
display one digit integers with a leading zero.

Best wishes Harald

The OP:-
extracts only the middle two numbers
^^^^^^
Harald:-
MID is a text function, so just for the fun of it a pure numeric approach
too:

As I pointed out, the original question was not succinct wrt exactly what
was to be returned, and besides that it was Saturday and Harald was bored.
:-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Michael Bednarek" wrote in message
...
On Sat, 19 Feb 2005 22:50:27 +0100, "Harald Staff"
wrote in microsoft.public.excel.misc:

MID is a text function, so just for the fun of it a pure numeric approach
too:

=INT(MOD(A1,1000)/10)

That MID() is a text function doesn't seem to matter - TEXT() seems
implied. To wit: =MID(5062,2,2) gives 06 which is what the OP wanted.

Your function omits the leading zero.

"gregork" skrev i melding
...
From a four digit number I want to have a formula that extracts only
the
middle two numbers eg." 5062" would equal "06" or "4113" would equal
"11".

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Harald,

... Solutions posted here are also archived and used by readers which
may have slightly different needs

Yes, Over the years I did so 712 times! Learned a lot of all those posts and
answers and even could sometimes help somebody else with an item out of my
archive.

So please keep on doing the right thing, guys like me (not clever enough to
invent all those solutions myself, sometimes even not understanding them
fully) are grateful for it.

Jack Sons
The netherlands


"Harald Staff" schreef in bericht
...
"Michael Bednarek" skrev i melding
...

That MID() is a text function doesn't seem to matter - TEXT() seems
implied. To wit: =MID(5062,2,2) gives 06 which is what the OP wanted.

Relax man. Solutions posted here are also archived and used by readers
which
may have slightly different needs, so alternate approaches to a problem is
always good and nothing to get worked up about. I know your solution works
fine and that it probably solved the initial problem. Just wanted to say
that a text approach to number material is not the only way to go. Text
functions are e.g. nothing but trouble when we calculate date and time..

Your function omits the leading zero.

As we both know, 6 and 06 is the same number. Custom number format 00 will
display one digit integers with a leading zero.

Best wishes Harald