buggy function in excel, please help !!!!

[bodbod]

Hi,

I am encountering an issue with excel regarding a formula which is applied automatically when I do not want it, i am explaining below :
I have some data coming from a huge database in an excel file, I am applying a text formula to extract a segment of this information and it gives me a mix of 3 alphanumerical entries.
This result is the basis of a vlookup in another base and something goes wrong as soon as the alphanumerical result is containing : a figure, then "E", then another figure.
ex : 7E0, 6E5, 9E2... in fact excel converts this in memory to a number : 7E0 is equal to 7exp0 = 7 and then do the vlookup and give me the wrong result.
how can we disable this E function, i thought only EXP() was the good one for exponential calcs. is there a trick to obtain the real value i am looking for : 7E0 for example and not 7 ?

thanks a million

 

[mikerickson]

Have you tried formatting the cells that receive the data as Text before the data is put in it?

 

[Norie]

How exactly is the data getting put into a cell?

Depending on how that's happening formatting the destination cell(s) as Text before any data goes in them should work.

By the way E is not a function, it's just a common symbol and it's not just used in Excel.

 

[bodbod]

Hey thx for the quick feedback,

yes i have tried the formatting and refreshed the calculation but it is still doing my bug

the "E70" is the result for instance of: =right(D5,3) where D5 is : 12345.XMF.23453.E70 (gerenal format) or txt ... i tried.

Can we attach a file in here ? otherwise i ll upload it on public server if needed to see the exact issue

thx

 

[Norie]

I think I might have misinterpreted what you mean.

Is the E70 the result of a formula?

If It is can you post the formula?

Also, are you sure this is what's causing the problem with your VLOOKUP?

 

[bodbod]

ok problem solved by entering the value : 'E70 in the database which is scanned by the vlookup formula. Even if the column containing the ref is in text format, this one was not really taken in consideration so i have edited manually by adding the ' before and this is forcing the cell to be in text mode...

thanks for the support
G