Mid function returns odd results.

I'm using the following phrase out of a much longer expression to
demonstrate that the return value of the MID function seems to be nothing I
can understand.

This expression:

=MID("6/10x",1,FIND("x","6/10x)-1))

returns 6/10 as text which makes sense. However I was under the impression
that when text in Excel contains numeric characters, it can be interpreted
as a number. So then how does the following expression,

=MID("6/10x",1,FIND("x","6/10x)-1)*1

return the result 38878? Does it still think it's text?

Thanks for your reply.
--
David Farber
L.A., CA

It's because if you calculate a value that excel can interpret as a number
it will convert it to that number and since the value is 6/10 excel will
interpret it as June 10th 2006 if your regional settings are US date format
and since 0 is Jan 0 1900 this means that June 10th 2006 is 38,787 days
since that date. So Excel thinks it is a date and if you format it as a date
you'll see that

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"David Farber" <[email protected]> wrote in message
news:[email protected]...
> I'm using the following phrase out of a much longer expression to
> demonstrate that the return value of the MID function seems to be nothing
> I
> can understand.
>
> This expression:
>
> =MID("6/10x",1,FIND("x","6/10x)-1))
>
> returns 6/10 as text which makes sense. However I was under the impression
> that when text in Excel contains numeric characters, it can be interpreted
> as a number. So then how does the following expression,
>
> =MID("6/10x",1,FIND("x","6/10x)-1)*1
>
> return the result 38878? Does it still think it's text?
>
> Thanks for your reply.
> --
> David Farber
> L.A., CA
>
>
>

6/10 (second example) is interpreted as 10/6/2006 (10 Jun) which is 38878 in
Excel internal date format.


"David Farber" wrote:

> I'm using the following phrase out of a much longer expression to
> demonstrate that the return value of the MID function seems to be nothing I
> can understand.
>
> This expression:
>
> =MID("6/10x",1,FIND("x","6/10x)-1))
>
> returns 6/10 as text which makes sense. However I was under the impression
> that when text in Excel contains numeric characters, it can be interpreted
> as a number. So then how does the following expression,
>
> =MID("6/10x",1,FIND("x","6/10x)-1)*1
>
> return the result 38878? Does it still think it's text?
>
> Thanks for your reply.
> --
> David Farber
> L.A., CA
>
>
>
>

Is there a way to override this behavior?

"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:%[email protected]...
> It's because if you calculate a value that excel can interpret as a number
> it will convert it to that number and since the value is 6/10 excel will
> interpret it as June 10th 2006 if your regional settings are US date
format
> and since 0 is Jan 0 1900 this means that June 10th 2006 is 38,787 days
> since that date. So Excel thinks it is a date and if you format it as a
date
> you'll see that
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "David Farber" <[email protected]> wrote in message
> news:[email protected]...
> > I'm using the following phrase out of a much longer expression to
> > demonstrate that the return value of the MID function seems to be
nothing
> > I
> > can understand.
> >
> > This expression:
> >
> > =MID("6/10x",1,FIND("x","6/10x)-1))
> >
> > returns 6/10 as text which makes sense. However I was under the
impression
> > that when text in Excel contains numeric characters, it can be
interpreted
> > as a number. So then how does the following expression,
> >
> > =MID("6/10x",1,FIND("x","6/10x)-1)*1
> >
> > return the result 38878? Does it still think it's text?
> >
> > Thanks for your reply.
> > --
> > David Farber
> > L.A., CA
> >
> >
> >
>
>

Just a shot in the dark - can you concatenate a "=" in there somehow so Excel
sees "=6/10" instead of just "6/10"?

Ken

"David Farber" wrote:

> Is there a way to override this behavior?
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:%[email protected]...
> > It's because if you calculate a value that excel can interpret as a number
> > it will convert it to that number and since the value is 6/10 excel will
> > interpret it as June 10th 2006 if your regional settings are US date
> format
> > and since 0 is Jan 0 1900 this means that June 10th 2006 is 38,787 days
> > since that date. So Excel thinks it is a date and if you format it as a
> date
> > you'll see that
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > Excel 95 - Excel 2007
> > Northwest Excel Solutions
> > www.nwexcelsolutions.com
> > "It is a good thing to follow the first law of holes;
> > if you are in one stop digging." Lord Healey
> >
> >
> > "David Farber" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm using the following phrase out of a much longer expression to
> > > demonstrate that the return value of the MID function seems to be
> nothing
> > > I
> > > can understand.
> > >
> > > This expression:
> > >
> > > =MID("6/10x",1,FIND("x","6/10x)-1))
> > >
> > > returns 6/10 as text which makes sense. However I was under the
> impression
> > > that when text in Excel contains numeric characters, it can be
> interpreted
> > > as a number. So then how does the following expression,
> > >
> > > =MID("6/10x",1,FIND("x","6/10x)-1)*1
> > >
> > > return the result 38878? Does it still think it's text?
> > >
> > > Thanks for your reply.
> > > --
> > > David Farber
> > > L.A., CA
> > >
> > >
> > >
> >
> >
>
>
>

That result is not much better: "=38878"
"kcoakley" <[email protected]> wrote in message
news:[email protected]...
> Just a shot in the dark - can you concatenate a "=" in there somehow so
Excel
> sees "=6/10" instead of just "6/10"?
>
> Ken
>
> "David Farber" wrote:
>
> > Is there a way to override this behavior?
> >
> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > news:%[email protected]...
> > > It's because if you calculate a value that excel can interpret as a
number
> > > it will convert it to that number and since the value is 6/10 excel
will
> > > interpret it as June 10th 2006 if your regional settings are US date
> > format
> > > and since 0 is Jan 0 1900 this means that June 10th 2006 is 38,787
days
> > > since that date. So Excel thinks it is a date and if you format it as
a
> > date
> > > you'll see that
> > >
> > > --
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > Excel 95 - Excel 2007
> > > Northwest Excel Solutions
> > > www.nwexcelsolutions.com
> > > "It is a good thing to follow the first law of holes;
> > > if you are in one stop digging." Lord Healey
> > >
> > >
> > > "David Farber" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I'm using the following phrase out of a much longer expression to
> > > > demonstrate that the return value of the MID function seems to be
> > nothing
> > > > I
> > > > can understand.
> > > >
> > > > This expression:
> > > >
> > > > =MID("6/10x",1,FIND("x","6/10x)-1))
> > > >
> > > > returns 6/10 as text which makes sense. However I was under the
> > impression
> > > > that when text in Excel contains numeric characters, it can be
> > interpreted
> > > > as a number. So then how does the following expression,
> > > >
> > > > =MID("6/10x",1,FIND("x","6/10x)-1)*1
> > > >
> > > > return the result 38878? Does it still think it's text?
> > > >
> > > > Thanks for your reply.
> > > > --
> > > > David Farber
> > > > L.A., CA
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >

Why are you using *1 ?

> =MID("6/10x",1,FIND("x","6/10x)-1)*1

That is what is converting 6/10 to a date string.

Biff

"David Farber" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to override this behavior?
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:%[email protected]...
>> It's because if you calculate a value that excel can interpret as a
>> number
>> it will convert it to that number and since the value is 6/10 excel will
>> interpret it as June 10th 2006 if your regional settings are US date
> format
>> and since 0 is Jan 0 1900 this means that June 10th 2006 is 38,787 days
>> since that date. So Excel thinks it is a date and if you format it as a
> date
>> you'll see that
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> Excel 95 - Excel 2007
>> Northwest Excel Solutions
>> www.nwexcelsolutions.com
>> "It is a good thing to follow the first law of holes;
>> if you are in one stop digging." Lord Healey
>>
>>
>> "David Farber" <[email protected]> wrote in message
>> news:[email protected]...
>> > I'm using the following phrase out of a much longer expression to
>> > demonstrate that the return value of the MID function seems to be
> nothing
>> > I
>> > can understand.
>> >
>> > This expression:
>> >
>> > =MID("6/10x",1,FIND("x","6/10x)-1))
>> >
>> > returns 6/10 as text which makes sense. However I was under the
> impression
>> > that when text in Excel contains numeric characters, it can be
> interpreted
>> > as a number. So then how does the following expression,
>> >
>> > =MID("6/10x",1,FIND("x","6/10x)-1)*1
>> >
>> > return the result 38878? Does it still think it's text?
>> >
>> > Thanks for your reply.
>> > --
>> > David Farber
>> > L.A., CA
>> >
>> >
>> >
>>
>>
>
>

I'm not really using *1 in my real world equation. It was just a
demonstration of how you cannot perform any arithmetic operation on a
fraction that gets interpreted as a date.

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Why are you using *1 ?
>
> > =MID("6/10x",1,FIND("x","6/10x)-1)*1
>
> That is what is converting 6/10 to a date string.
>
> Biff
>
> "David Farber" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a way to override this behavior?
> >
> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > news:%[email protected]...
> >> It's because if you calculate a value that excel can interpret as a
> >> number
> >> it will convert it to that number and since the value is 6/10 excel
will
> >> interpret it as June 10th 2006 if your regional settings are US date
> > format
> >> and since 0 is Jan 0 1900 this means that June 10th 2006 is 38,787 days
> >> since that date. So Excel thinks it is a date and if you format it as a
> > date
> >> you'll see that
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> Excel 95 - Excel 2007
> >> Northwest Excel Solutions
> >> www.nwexcelsolutions.com
> >> "It is a good thing to follow the first law of holes;

If 6/10x is in a cell can you use a custom format?

# ??/??"x"

Excel will treat it as 0.6 for calculation purposes, but will display 3/5x.
Otherwise, I think you'll have to use the left and right functions to
separate the numerator and denominator. Maybe there is a shorter way, but

LEFT(MID("6/10x",1,FIND("x","6/10x")-1),FIND("/",MID("6/10x",1,FIND("x","6/10x")-1))-1)/RIGHT(MID("6/10x",1,FIND("x","6/10x")-1),LEN(MID("6/10x",1,FIND("x","6/10x")-1))-FIND("/",MID("6/10x",1,FIND("x","6/10x")-1)))

"David Farber" wrote:

> I'm not really using *1 in my real world equation. It was just a
> demonstration of how you cannot perform any arithmetic operation on a
> fraction that gets interpreted as a date.
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
> > Why are you using *1 ?
> >
> > > =MID("6/10x",1,FIND("x","6/10x)-1)*1
> >
> > That is what is converting 6/10 to a date string.
> >
> > Biff
> >
> > "David Farber" <[email protected]> wrote in message
> > news:[email protected]...
> > > Is there a way to override this behavior?
> > >
> > > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > > news:%[email protected]...
> > >> It's because if you calculate a value that excel can interpret as a
> > >> number
> > >> it will convert it to that number and since the value is 6/10 excel
> will
> > >> interpret it as June 10th 2006 if your regional settings are US date
> > > format
> > >> and since 0 is Jan 0 1900 this means that June 10th 2006 is 38,787 days
> > >> since that date. So Excel thinks it is a date and if you format it as a
> > > date
> > >> you'll see that
> > >>
> > >> --
> > >>
> > >> Regards,
> > >>
> > >> Peo Sjoblom
> > >>
> > >> Excel 95 - Excel 2007
> > >> Northwest Excel Solutions
> > >> www.nwexcelsolutions.com
> > >> "It is a good thing to follow the first law of holes;
>
>
>

The custom formatting does not work.

"JMB" <[email protected]> wrote in message
news:[email protected]...
> If 6/10x is in a cell can you use a custom format?
>
> # ??/??"x"
>
> Excel will treat it as 0.6 for calculation purposes, but will display
3/5x.
> Otherwise, I think you'll have to use the left and right functions to
> separate the numerator and denominator. Maybe there is a shorter way, but
>

However the formula below works very nicely. That's quite a bit of work to
divide two numbers. I think I'll just change the data entry to decimals and
not give Excel a chance to consider my numbers as dates.

>
LEFT(MID("6/10x",1,FIND("x","6/10x")-1),FIND("/",MID("6/10x",1,FIND("x","6/1
0x")-1))-1)/RIGHT(MID("6/10x",1,FIND("x","6/10x")-1),LEN(MID("6/10x",1,FIND(
"x","6/10x")-1))-FIND("/",MID("6/10x",1,FIND("x","6/10x")-1)))

Thanks for figuring that out!



>
> "David Farber" wrote:
>
> > I'm not really using *1 in my real world equation. It was just a
> > demonstration of how you cannot perform any arithmetic operation on a
> > fraction that gets interpreted as a date.
> >
> > "Biff" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Why are you using *1 ?
> > >
> > > > =MID("6/10x",1,FIND("x","6/10x)-1)*1
> > >
> > > That is what is converting 6/10 to a date string.
> > >
> > > Biff
> > >
> > > "David Farber" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Is there a way to override this behavior?
> > > >
> > > > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > > > news:%[email protected]...
> > > >> It's because if you calculate a value that excel can interpret as a
> > > >> number
> > > >> it will convert it to that number and since the value is 6/10 excel
> > will
> > > >> interpret it as June 10th 2006 if your regional settings are US
date
> > > > format
> > > >> and since 0 is Jan 0 1900 this means that June 10th 2006 is 38,787
days
> > > >> since that date. So Excel thinks it is a date and if you format it
as a
> > > > date
> > > >> you'll see that
> > > >>
> > > >> --
> > > >>
> > > >> Regards,
> > > >>
> > > >> Peo Sjoblom
> > > >>
> > > >> Excel 95 - Excel 2007
> > > >> Northwest Excel Solutions
> > > >> www.nwexcelsolutions.com
> > > >> "It is a good thing to follow the first law of holes;
> >
> >
> >

Use

=EVAL(MID("6/10x",1,FIND("x","6/10x")-1))


EVAL() function from Add-ins morefunc.


or You can use EVALUATE method in range name the call range name to
use.
like this

create new range name AA and define AA =
EVALUATE(MID("6/10x",1,FIND("x","6/10x")-1))

in any cell you can use :

=AA

you will get the result as you want.


Regards,
VN.

"VN" <[email protected]> wrote in message
news:[email protected]...
> Use
>
> =EVAL(MID("6/10x",1,FIND("x","6/10x")-1))
>
>
> EVAL() function from Add-ins morefunc.
>
>
> or You can use EVALUATE method in range name the call range name to
> use.
> like this
>
> create new range name AA and define AA =
> EVALUATE(MID("6/10x",1,FIND("x","6/10x")-1))
>
> in any cell you can use :
>
> =AA
>
> you will get the result as you want.
>
>
> Regards,
> VN.
>

I downloaded the add-ins program and that EVAL() function did the trick!

--
Thanks for your reply.

David Farber
L.A., CA