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.

Note: you can either get the visible formatted result in the cell using .Text, or you can get the underlying unformatted value from the cell using .Value — Charles Williams, Apr 08 '11 at 09:11

Jean-François Corbett · Accepted Answer · 2011-04-11T12:23:22.427

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

The format of the output text being as for example while we would get while copying from the range and pasting in a notepad. — Thunder, Apr 11 '11 at 11:23
Edited answer to address this. I guess I got confused by the double "while". — Jean-François Corbett, Apr 11 '11 at 12:24

score 6 · Answer 2 · answered Apr 08 '11 at 06:53

6

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

This will give you each cell one after the other.

answered Apr 08 '11 at 06:53

stema

90,351
20
107
135

3

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. – Jon49 Apr 08 '11 at 13:34
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. – stema Apr 09 '11 at 21:57

Deepak Ninnar · Answer 3 · 2012-08-27T03:11:37.627

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

score 1 · Answer 4 · answered Apr 08 '11 at 05:52

1

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

answered Apr 08 '11 at 05:52

Friedrich

5,916
25
45

score 1 · Answer 5 · answered Apr 08 '11 at 06:11

1

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

answered Apr 08 '11 at 06:11

Jon49

4,444
4
36
73

This solution doesn't include the formatting that is required in question. This just extracts the value of the cells. – thanos.a Mar 15 '16 at 21:27

Get formatted values from a multi-cell range

5 Answers5

Linked