When text cells don't act like text cells

I've got a table of data in which I want one column to contain only text values of numbers. For example, I want values like 4700, 33, 2.7, 0.01, etc., to be represented not as numbers but as text. This is desired because I use a macro that expects to find text values in that column.

So I formatted the whole column of data as text, but found that the macro still didn't see the values as text.

Turns out that this is because once the numbers had been entered into the cells, that caused the cells to be set to numeric format, and this format was not affected by my subsequent attempt to set the cells' format to text.

To get around this, I cleared the cells, set their format to text, then entered the values.

Engineering notation

Scientific notation examples:

100:	1E2
1000:	1E3
10,000:	1E4
100,000:	1E5
1,000,000:	1E6
10,000,000:	1E7

Engineering notation examples:

100:	100
1000:	1E3
10,000:	10E3
100,000:	100E3
1,000,000:	1E6
10,000,000:	10E6

This custom cell format provides engineering notation with a variable number of decimal places:
##0.#E+0

This custom cell format provides engineering notation with 2 decimal places:
##0.00E+0

Purpose of this blog

...is to collect and present solutions, tips, and notes of interest regarding Microsoft Excel. Some of the material may be original, some borrowed (with references given).

If You have questions or contributions, feel free to leave a concise and constructive comment.