The other day a co-worker needed to convert the formatted numbers in cells to text strings in the same cells – text strings that still looked like the formatted cells. For example, a cell with the number 12.34 formatted as a dollar amount would be converted to the string “$12.34”. The same number formatted for percent with two decimal points would become the string “1234.00%”. Formatted as a date, it would become the string “1/12/1900 8:09:36 AM”. In other words, he wanted text strings that contain what your eyes see in the formatted cell.

So that this… becomes that.

It’s subtle, but you can see that the converted cells are now left-aligned and many of them have the green triangle indicating “number stored as text.” And the ISNUMBER formula in D2 has switched from TRUE to FALSE.

My co-workder needed this because his worksheet was feeding an ArcGIS web-based map, with the cell values being used for interactive tables, or something like that. With formatted numbers in the cells, the tables would show 12.34 for the above examples. By converting the numbers to strings, the data were correctly formatted.

I don’t know of any way to do this without VBA. In Excel we are able to format cells as text, but if you just apply the Text format to numbers you lose the formatting. If you format target cells as Text and then copy your formatted source cells over them, the target cells assume the source cells’ formatting. Pasting as Values loses the formatting. Converting to a csv loses the formatting. So, I think VBA is required for this.

The VBA Range object has a Text property, which returns the contents of the cell as they appear to the human eye, exactly what we want. Using this property I was able to write a few lines of code and convert my co-worker’s spreadsheet:

Since the number of cells was very small, this ran quickly and did what he wanted. The cells were now pushed into the interworld with their formatting intact.

Looping through cells as above is quite slow. Running it on 20,000 rows with 40,000 cells takes about 20 seconds on my laptop. Ideally, you want to assign all the cells to a variant array, process the elements of the array, and then plunk the array back into the range of cells.

I’ve done this with the Range object’s Value2 property before and wrote some code to do the same with Text. However, my variant array kept returning Null after doing something like:

I found this Charles Williams post where he points out that, unlike Value or Value2, assigning the Text property of a range to a variant array returns Null, unless all the cells have the same value and format.

So I changed the code to loop through the cells one at a time and assign their Text property to a two-dimensional String array. Fortunately, we can still assign the whole array back to a range.

Charles’ post revealed another interesting gotcha: when looping through cells’ Text properties and assigning them to an array your code gets progressively slower, but only if the range has rows with different heights in it. In my testing I noticed that even if all the rows are set back to the same height this weirdness persists.

The solution is to, every so often, select the cell that’s being processed. In my case, I chose to do it every 1000 rows. This won’t work if ScreenUpdating is set to False. This creates an additional reason to not process the cells one at a time, as all those writes back to the spreadsheet would slow things down even more.

(That’s a funky bug isn’t it? Makes you use Select in your code and leave Screenupdating on. I swear, when I started, I thought this would be a 400 word post! Nothing is simple in Excel, at least nothing I write about.)

One other issue is that if your columns are too narrow for a number and a cell is displaying “####”, the resulting text string will be “####”. I included a line in the code below to autofit the columns, but I’m not sure it will fix every situation.

With this code 20,000 rows with 40,000 cells takes about four seconds. This is only slightly worse than the three seconds it takes if the row heights are all the same and the Select fix isn’t needed, and much better than the 20 seconds if the row heights are different and Charles’ Select fix isn’t used.