• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell Formatting query

M

MGCummins

New Member
Hi, I am trying to enter data in a cell which is alpha/numeric but when I use the letter E it changes format to Scientific from General and changes the display - e.g. 13E1 when entered displays as 1.30E+02 - Any other letter is fine - eg 13A1, 13B1 etc. If I use the format painter from a cell that displays correctly and paste it over the data, it changes the display to 130 - I have tried creating a custom format but get dialog box saying excel cannot use the format you typed - any suggestions? thank you.
 
sorry - update on last post - because the cell error is showing this is having a knock on affect when using vlookup - is there anyway to amend cell format to accept the letter E when entering data.
 
Hi ,

Can you please give a sample VLOOKUP formula , specifying the data in the first and second parameters e.g. if your VLOOKUP formula is :

=VLOOKUP(lookup_value , lookup_range , return column number , FALSE)

what is the value in the lookup_value cell reference , what is the data in the lookup_range ?

Narayan
 
Hi ,

If you have a problem only with those inputs which have E in them , just enter them using a single quote symbol first ; thus to enter the data 13E1 , press the single quote symbol key ' first , and then type in 13E1.

Narayan
 
Hi - sorry for not getting back yesterday - after I entered e.g. 6e5 as data value, I got cellular reference warning and chose to ignore - which removed small green triangle from cell - however when entering the lookup value of 6e5 it seems to be changing it to 600000 which of course is throwing the vlookup out. Unfortunately, I can't upload a file - but in the Lookup_value field I entered 6e5 and over on the right hand side it is displaying = 600000 when I click on ok I just get the #VALUE! displaying in the cell I tried to run the vlookup in.
 
Hurray - I've finally got it working - after ignoring the cellular reference in the cell containing the letter e - when completing the vlookup function i clicked on the Cell to enter the data rather than typing it into the Lookup_value field and this works fine - Also tried the use of the ' before and after the data containing the letter 3, which saved me having to change format of cell - very happy! Happy valentines day to you all and thanks for all the help :) <3
 
You must log in or register to reply here.
Back
Top