Convert range of formatted numbers to text strings (keeping formatting when pasting, but convert to text)

Question

I have a large range of numbers with varying number formatting. I would like to change all numbers in the array, including their formatting, into text strings (so that formatting characters like '$' are stored as a string in each cell, not as an integer with formatting applied).

The goal is to be able to store these formatted strings so that I can later access them directly (i.e., using R or Python or mailmerge), while keeping all of the number formatting easily available.

In the image below you can see that the stored value is actually 1001 (as a numeric value).

I want a way to change arrays of values (i.e., C2, D2, and E2) to match the format shown in cell C4 below. In this format, all formatting characters are stored as a text string, not as number formatting. The equation bar correctly shows that the value is a text string "$1,001".

Pasting as value, or pasting as value with number formatting, does not produce the desired result. Using .text in VBA reads the cell value and the number formatting, but does not transform the numbers and formatting characters ("$1,001") into a string, as desired. Copying data into notepad, then copying back into excel does produce the desired result.

Is there a way in Excel (using VBA if necessary) to change arrays of formatted numbers to text strings, as described above?

Answer 1

Create an array of string of the .Text then format the range as text and paste the array back:

    Sub ttt()
        Dim rng As Range
        Set rng = ActiveSheet.Range("A1:A10")
        
        Dim t() As String
        ReDim t(1 To rng.Rows.Count, 1 To rng.Columns.Count) As String
        
        Dim i As Long
        For i = 1 To UBound(t, 1)
            Dim j As Long
            For j = 1 To UBound(t, 2)
                t(i, j) = rng.Cells(i, j).Text
            Next j
        Next i
        
        
        rng.NumberFormat = "@"
        rng = t
    End Sub

Before:

After:

Answer 2

Quick alternative to @ScottCraner 's helpful approach via XML spreadsheet

Alerted by @FunThomas ' comment that incompletely visible numeric values (shown e.g. as ####) also result in a truncated "####" string after conversion to .Text, I demonstrate a simple way out via

Range.Value(xlRangeValueXMLSpreadsheet), a.k.a. Value(11) property

• returning a wellformed xml content string including all necessary type & style indications and
• allowing a quick type conversion from numbers to strings (here by a simple replace action) without the need to check for not fully displayed number values.

Sub Num2Txt(src As Range, Optional tgt As Range)
    If IsMissing(tgt) Then Set tgt = src        ' overwrite target by default
'a) change number type in wellformed xml spreadsheet content, aka Value(11)
    Dim xml As String
    xml = Replace(src.Value(xlRangeValueXMLSpreadsheet), "=Number", "=String")
'b) write converted xml spreadsheet content to target
    tgt.Value(xlRangeValueXMLSpreadsheet) = xml ' same as: tgt.Value(11) = xml
End Sub

Example call

To overwrite the source range use Num2Txt rng with only one argument overwriting the target range by default, passing a second argument allows to define any target range.

    Dim rng As Range
    Set rng = Tabelle2.Range("A1:A10")  ' set (source) range
    Num2Txt rng, rng.Offset(, 2)        ' copy e.g. 2 columns to the right 
'   Num2Txt rng                         ' overwrite source range   

MS Help reference

• Range.Value property
• XlRangeValueDataType enumeration