Mid function returns odd results.

[David Farber]

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.

 

[Peo Sjoblom]

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

 

[Guest]

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

 

[David Farber]

Is there a way to override this behavior?

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

 

[Guest]

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]

That result is not much better: "=38878"

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

Ken

 

[Biff]

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]

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.

 

[Guest]

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]

The custom formatting does not work.

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!

 

[Krishnakumar]

Little shorter..

=--LEFT("6/10x",FIND("/","6/10x")-1)/--MID("6/10x",FIND("/","6/10x")+1,SUM(FIND({"/","x"},"6/10x")*{-1,1})-1)

HT

 

[VN]

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.

 

[David Farber]

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!