Problem with leading zero's, scientific notations, and pasting into a form

[kerry590]

I am having a problem with a sheet I use for work, merging the data I get from a downloaded source correctly(also an excel format). I have been in the process of optimizing it to help catch errors before it even gets printed and have run into some roadblocks I can't figure out.

The first error is with leading zeros. One of the cells is a 6 digit number that identifies a department (org) and there is a merged cell at the top of the page that shows the name of the said department using a VLOOKUP function. I was able to partially correct the issue on the department database sheet (the sheet that VLOOKUP uses to find the dept) by formating the numbers using format cell > Number > Custom > 000000. But this doesn't help me on the cover sheet side as when I paste the information in from the downloaded sheet it wipes out any cell formatting I had in place. So that leads to the error of the header showing the wrong department because if VLOOKUP doesn't match it goes to the closet match which I do not know how to fix. I have set some conditional formatting up to highlight any cells with digits under 6 so that helps to some degree. A few of the other columns ('E' 3Digits, 'L'-'O' 6 Digits) have the same problem just to a lesser degree, when I paste information into them any leading zeros get wiped out. I am trying to avoid having to go in and type an apostrophe in when this happens to convert it to text. 

The next problem is of a similar nature I guess when I paste the data in sometimes the serial number if long enough is converted into scientific notation. This is again solved by simply typing an apostrophe but I would like to avoid that if possible. I do not know how to simulate a scientific notation on the attached spreadsheet.

[#2]

The downloaded file will look something like this one...

[#3]

@kerry590 

Hi

The conditional formatting doesn't change the actual content of the cells but just makes them 'appear' different. So if you conditionally format a number e.g. 234 to have a leading zero, if will look show 0234 in the cell but if you click into the cell and look at the formula bar you will see that the actual content of the cell is still 234 and not 0234. Do you need to display the leading zeros? Are they in the original file you are copying from? have you explored all the 'paste as' options? Are you just pasting values? Finally, should your vlookup contain a 'true' argument to make it look for an exact match only?

[#4]

@kerry590 Hi I have tried to replicate your problem by pasting the data into the cover sheet but none of the values caused any issue. What formatting do the numbers actually have when they appear? Are they formatted as numbers or general? In which case I don't understand how the vlookup wouldn't find it.. Have you considered pasting only the values rather than values and formatting? Also, have you considered  pulling the data from another sheet into the cover sheet using formulas to ensure consistency? you could pull all the values you need as number through using the convert from text formula (value()) and make sure that the cells are correctly formatted?

[#5]

@kerry590 

In department list you may convert numbers to texts such way

and use column with formulas as lookup array, values here will be kept as texts.

In downloaded file ORG are initially without leading zero

but even if they are, with opening csv file in Excel they are automatically converted to numbers, i.e. values without leading zeroes. To lookup them in departments list with texts as departments you may use

=VLOOKUP(TEXT(L6,"000000"),Departments!B2:C116,2,FALSE)

and I'd recommend to use FALSE as last parameter, i.e. use exact match instead of approximate one.