Get formatted values from a multi-cell range

Question

Dim myText As String
myText= Range("a3").Text

Returns the formatted value in cell A3, but

myText= Range("a3:c7").Text

gives me an error.

How do I get strings representing formatted values from a multi-cell range, while preserving the number format? i.e. the format of the output text would be the same as if copy-pasting from the range to a text editor.

Solution 1

The only way to get multiple cell values into an array with one single statement (no loops) is with a Variant array.

Dim varItemName As Variant
varItemName = Range("a3:c7")

If you really absolutely need the names to be type String, then just CStr them later when you use them.

output = FunctionRequiringStringArgument(CStr(varItemName(1,2))

---

EDIT: Okay, okay, you want strings with same format as in sheet.

Here's a full working example.

Dim strMyFormat1 As String
Dim varItemName As Variant
Dim strItemName() As String
Dim strItemNameBF() As String
Dim iCol As Long
Dim iRow As Long
Dim rngMyRange As Range

Set rngMyRange = Range("A3:C7")
varItemName = rngMyRange
ReDim strItemName(LBound(varItemName, 1) To UBound(varItemName, 1), _
    LBound(varItemName, 2) To UBound(varItemName, 2))

'// Take a sample of the format
strMyFormat1 = Range("A3").NumberFormat

'// Apply format sample to all values
For iRow = LBound(varItemName, 1) To UBound(varItemName, 1)
    For iCol = LBound(varItemName, 2) To UBound(varItemName, 2)
        strItemName(iRow, iCol) = Format(varItemName(iRow, iCol), strMyFormat1)
    Next iCol
Next iRow
'// Can also apply to only some values -- adjust loops.
'// More loops go here if many format samples.

'// If all cells have different formats, must use brute force -- slower.
ReDim strItemNameBF(1 To rngMyRange.Rows.Count, _
    1 To rngMyRange.Columns.Count)
For iRow = 1 To rngMyRange.Rows.Count
    For iCol = 1 To rngMyRange.Columns.Count
        strItemNameBF(iRow, iCol) = rngMyRange.Cells(iRow, iCol).Text
    Next iCol
Next iRow

Solution 2

For Each c In Range("a3:c7")
    ItemName = c.Text
Next c

This will give you each cell one after the other.

Solution 3

This is a modified version of one of the post here and it worked for me.

    Function Range2Text(ByVal my_range As Range) As String
        Dim i As Integer, j As Integer
        Dim v1 As Variant
        Dim Txt As String

        v1 = my_range
        For i = 1 To UBound(v1)
            For j = 1 To UBound(v1, 2)
                Txt = Txt & v1(i, j)
            Next j
            Txt = Txt & vbCrLf
        Next i

        Range2Text = Txt
    End Function

Solution 4

Make a collection and run through all the Areas of the range and collect the text into the collection.

Solution 5

dim i as integer, j as integer
Dim v1 as variant

v1=range("a3:c7")

for i=1 to ubound(v1)
  for j=1 to ubound(v1,2)
    debug.print v1(i,j)
  next j
next i

View more solutions

Comments

[Jon49]

If you have a large data set then looping through the range is incredibly slow. But just for a few cells it works. Also, using .Text can cut off some of your information since it only returns what the user sees.

[stema]

I got a down vote ????? OK, in my opinion this is the perfect, Excel build in solution to visit every cell in a range (for a small amount of cells). From the OP I don't know what he wants to do with the data. So the example in the question is about 15 cells ==> perfect solution. I know it is not fast to loop through cells. If there are large amount of datasets then load the Data into the memory (see Jean-Francois Corbetts answer). I personally work on my Data (~ 40000 large Datasets) using Perl and use Excel only for analyses and visualization.

[Thunder]

The format of the output text being as for example while we would get while copying from the range and pasting in a notepad.

[Jean-François Corbett]

Edited answer to address this. I guess I got confused by the double "while".

[thanos.a]

This solution doesn't include the formatting that is required in question. This just extracts the value of the cells.