Hi All, I'm acontaining 'E' and converts it to exponential format instead of remaining as text. part of a small engineering company that collects home energy efficiency parameters throughout our Province as part of a Federal Grant to homeowners who upgrade their homes. We use software provided by the Government, and it in turn yields a TSV of all the information on each house - about 3/4 of a page in Notepad. We have an Excel sheet with a macro that is supposed to import the data from the TSV, and puts them into columns that have conditional formatting that flags ( turns the background red ) on any cell that has a value outside fixed ranges. This all worked flawlessly when all the houses had ID numbers in the form of 1510A00234, and 1510B00234. The numberering scheme is set Federally, and the first two digits ( 15 ) represent our Province. the next two digits represent the ID number of our Field Evaluator ( We have 22 currently testing homes ). The character A or B represents the first evaluation(A), or the follow-up evaluation(B) after the homeowner has done upgrades to the home ( furnace, windows, insulation, etc )
The program changed gears last year and the evaluations now carry a D or E in the ID number, and the problem occurs when we feed ID's containing an E.
For example: 1523E00183 is converted to 1.52E+186 in column D.
I fiddled with the macro, and was able to see that it creates a new Excel workbook, imports the TSV, and copies that data into the TSV Checker.xls The problem goes away once the house number exceeds about 320 - a house file of 1523E00340 maintains it's form of text without changing to an Exponential Value.
We can use the TSV Checker.xls to point to about 25 TSV files at once. This exponential problem has caused about 3000 E files to hit a bottle-neck so assistance is greatly appreciated.
I'm including the TSV Checker, as well as a sample TSV file that the Gov't housing software generates.
We use the TSV Checker to make sure dozens of parameters are within expected bounds before we submit the data to Ottawa - otherwise their own 'TSV checker' kicks it back to us with 'data out of range errors'