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?

CodePudding user response：

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:

CodePudding user response：

I have just made a simple formula to achieve $0,000.00 string instead of formatting.

Click this picture link: "Concate" and "Text" Formula

Formula:

="$"&TEXT(E6,"0,000.00")

Check out these 2 images,,,, to Explain this scenario:

Click this picture link: Formatting number as "$0,000.00" which obviously doesn't fulfill the purpose

Click this picture link: As you want, but this is formula, let's test it as shown in next image

Click this picture link: The test succeeded: the result of formula when pasted as "Paste Value", you can see the result as "$0,000.00" string instead of format.

try it and let me know if you need any help.