Copy Numbers With Formatting to Strings

Copy Numbers With Formatting to Strings

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:

Dim cell as Excel.Range
For Each cell In Selection
   CellText = cell.Text
   '@ is the Text format
   cell.NumberFormat = "@"
   cell.Value2 = CellText
Next cell

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:

Dim varCells as Variant
varCells = Selection.Text

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.

Sub NumberToStringWithFormat(rng As Excel.Range)
Dim Texts() As String
Dim i As Long, j As Long

'This might prevent "###" if column too narrow
rng.EntireColumn.AutoFit
'Can't use variables in Dim
ReDim Texts(1 To rng.Rows.Count, 1 To rng.Columns.Count)
For i = 1 To rng.Rows.Count
    'Charles' fix for slow code with Text
    If i Mod 1000 = 0 Then
        rng.Range("A1").Offset(i).Select
    End If
    For j = 1 To rng.Columns.Count
        Texts(i, j) = rng.Cells(i, j).Text
    Next j
Next i
'@ is the Text format
rng.NumberFormat = "@"
rng.Value2 = Texts
End Sub

3 thoughts on “Copy Numbers With Formatting to Strings

  1. Thanks for the article, interesting as always. Thought I’d have a go at your challenge “I don’t know of any way to do this without VBA”:

    Using the format codes listed here: http://www.techonthenet.com/excel/formulas/cell.php

    B1
    =CELL("format",A1)
    C1
    =TEXT(A1,
    IF(B1="F0","0",
    IF(B1=",0","#,##0",
    IF(B1="F2","0.00",
    IF(B1=",2","#,##0.00",
    IF(B1="C0","$#,##0_);($#,##0)",
    IF(B1="C0-","$#,##0_);[RED]($#,##0)",
    IF(B1="C2","$#,##0.00_);($#,##0.00)",
    IF(B1="C2-","$#,##0.00_);[RED]($#,##0.00)",
    IF(B1="P0","0%",
    IF(B1="P2","0.00%",
    IF(B1="S2","0.00E+00",
    IF(B1="D4","mm/dd/yy",
    IF(B1="D1","dd-mmm-yy",
    IF(B1="D2","dd-mmm",
    IF(B1="D3","mmm-yy",
    IF(B1="D5","mm/dd",
    IF(B1="D7","h:mm AM/PM",
    IF(B1="D6","h:mm:ss AM/PM",
    IF(B1="D9","h:mm",
    IF(B1="D8","h:mm:ss","GENERAL")))))))))))))))))))))

    Copy and Paste Values

    • Ben, thanks for taking up the challenge! The Cell function is a great idea.

      Your formulas works for the most part, and where they fall down it seems to be because the Cell function has no “info” format type that matches. The oddest is that 1/12/1900 comes out as 1/12/00. It uses D4, which is what the documentation says. I don’t see any format that includes “yyyy.”

      The formula also turned Accounting to Currency and sees fractions as General. It also sees Date and Time together, e.g., 1/12/1900 8:09:36 AM, as General, but that works as it ends up looking the same. And it didn’t catch the word wrap I put in the long date by hitting Alt-Enter before “AM.”

      Everything else, including Currency, Percent and scientific notation was spot on.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>