Different Value appears in Function Box

[R J Solanki]

Hi to all,

I have received data from my friend in excel 2007 sheet, where in one of the column i have found that
In Cell of that column : M2500025; whereas in function box it appears only 25
I have tried lot & i have found that in format cell function in customs the following numbers have been inserted M2500000, may be such problem appears because of it

Now what i want that what the numbers appears in cell (i.e. M2500025) shall also appears in Function box (i.e. M2500025 should appear in function box instead of 25) so that i can apply formula of Vlookup

Thanks in advance

 

[RoryA]

You can use the Text function:
=VLOOKUP(TEXT(A1,"""M25""00000"),table,column,False)