MID function

[yaparala]

1.A1 having 1 and A2 having 1
comparing A1,A2 using IF function(=IF(A1=A2,"Y","N"). it gives correct result like 'Y'.

same as above

2. G1 having 23456 applied G2=MID(G1,1,1) it gives value is 2 in G2.
now i compare values G2 and A2 using IF function(=IF(G2=A2,"Y","N") gives incorret result like 'N'.

please find the attached sheet.

thanks for advance

 

[NARAYANK991]

Hi ,

That is because one is a numeric value , while the other is a text value.

You can verify this for yourself ; put the number 5 in A1 ; format cell B1 as text , and enter 5 in it ; now in C1 put =A1=B1 ; you should get FALSE as the result.

Regarding your second example , G2 contains the text value 2 ; A2 contains 1 , it doesn't matter whether it is numeric or text. If you compare A2 and G2 , the result will be FALSE , as a result of which you should get "N". The output is the expected one.

Narayan

 

[yaparala]

Thanks for reply,

but if i using same If function (=IF(G2-A2=0,"Equal","not Equal") it gives correct value.
here also i am using same text

 

[yaparala]

Hi ,

That is because one is a numeric value , while the other is a text value.

You can verify this for yourself ; put the number 5 in A1 ; format cell B1 as text , and enter 5 in it ; now in C1 put =A1=B1 ; you should get FALSE as the result.

Regarding your second example , G2 contains the text value 2 ; A2 contains 1 , it doesn't matter whether it is numeric or text. If you compare A2 and G2 , the result will be FALSE , as a result of which you should get "N". The output is the expected one.

Narayan

Thanks for reply,

but if i using same If function (=IF(G2-A2=0,"Equal","not Equal") it gives correct value.
here also i am using same tex

 

[NARAYANK991]

Hi ,

I think there is some confusion ; I do not know what is your definition of correct value.

If I put the formula :

=IF(G2-A2=0,"Equal","not Equal")

in a cell , Excel displays not Equal , which is correct because G2 is not equal to A2 ; it does not matter whether you are comparing a text value and a numeric value , since the values themselves are unequal.

1. When you compare a number say 7 , with a text value of 7 , you will get FALSE.

2. When you compare a number 7 with another number not equal to 7 , you will get FALSE.

3. When you compare a text value 7 with another text value not equal to 7 , you will get FALSE.

4. When you compare a number say 7 with a text value not equal to 7 , you will get FALSE.

According to you , which of the above is correct , and which is not correct ?

Narayan

 

[NARAYANK991]

Hi ,

Just to add to what I have already posted , suppose A1 has the numeric value 2.

Suppose A2 has the text value 2 ; you can enter this text value in two ways viz. type the single quote ( apostrophe ) and then type 2 and press ENTER , or format the cell A2 as Text and then enter the value 2.

Now , in A3 , enter the formula : =IF(A1=A2,"Equal","Not Equal")

In A4 , enter the formula : =IF(A1-A2=0,"Equal","Not Equal")

The two results are different !

This is because when we carry out an arithmetic operation , as we do in A1-A2 , then the text value is implicitly converted to a numeric value ; therefore when the subtraction is carried out , the result is 0.

Narayan

 

[yaparala]

Hi ,

Just to add to what I have already posted , suppose A1 has the numeric value 2.

Suppose A2 has the text value 2 ; you can enter this text value in two ways viz. type the single quote ( apostrophe ) and then type 2 and press ENTER , or format the cell A2 as Text and then enter the value 2.

Now , in A3 , enter the formula : =IF(A1=A2,"Equal","Not Equal")

In A4 , enter the formula : =IF(A1-A2=0,"Equal","Not Equal")

The two results are different !

This is because when we carry out an arithmetic operation , as we do in A1-A2 , then the text value is implicitly converted to a numeric value ; therefore when the subtraction is carried out , the result is 0.

Narayan

Hi Narayan,

i got clear idea.

thank for ur help..............