VBA to change room numbers from Scientific Notation to text

[powercell99]

Need help, when we run a report from our work database, and export it to Excel, the room numbers get screwed up because of scientific notation. the Room numbers format are 5 characters in length, characters 1, 3, 4, 5 are numbers, and the 2nd character is a letter. the problem is for all rooms in the "E" wing causes excel to change the number format to scientific notation. 9E54 = 9E+54. we tried changing the number format for the entire column to text, number, etc, but excel keeps changing it back to Sci Notation. We tried find and replacing the "E+" to just "E" but again it changes it back. reading online, i see many people suggest pre-formatting outside of excel to make that a text field, but we dont have that option of pre-forming. assuming the room numbers are in column D, does anyone have any suggestions on how to deal with Excel autochanging the number formats to Scientific Notation????

Thanks for your thoughts.

 

[mikerickson]

Try formatting the cells as Text.