Wow! ... my email was down and I just got back up. I am impressed by all the responses. I will have to go try all these out.
To give a little more info, I am working with an Excel add-in (API/DLL) that serves as an interface to query Hyperion/Essbase. Since all the analysts I support live and breath Excel, I have to use worksheets to get the data OR they have already pulled the data via this add-in and I get stuck making things look pretty (annoying cosmetics!).
Since I am relatively new to this add-in, I don't know all the "in"s and "out"s. There may be a way to make the data come out consistently numeric or consistently numbers as text. Unfortunately, it is
always mixed. For appearance purposes, the text numbers left align and the real numbers right align.
I typically get all the data on one sheet (less than 65,000+ records), but sometimes, I get someone else's Excel file and the data dump is on multiple sheets with other worksheets that have formulas, so I wouldn't want the macro to work on more than one sheet and only on the ones I select.
When I try to manually convert them, nothing seems to convert them other then individually clicking on the "smart tags" and using that option to convert. Unfortunately, that is one cell at a time. The best that Excel help could offer is to type the correct number in an unused cell, copy it, hold down CTRL, click on all the cells that are text numbers, then paste (almost as time consuming).
Strangely enough, in my case, most of the time, the "text numbers" are the number
ZERO.
As far as performance goes, I am willing to go take a break if I knew that when I came back, the text numbers were real numbers ... (the "weenie" way out), but that is because I have nothing better at the time.
I will try all of the things you suggested and let you know and dish out the appropriate "stars".
Many thanks to all!
--- Tom