Create an array string from range.

[G12Consult]

I have about 5000 numbers in a range.

I need to be able to create a string/array from these numbers.

Something like

'384848','373728', '384747'

What's the easiest and quickest way. Formula or VBA

Thanks
Andrew

 

[dhookom]

I would use vba since my formula knowledge is limited

Function RangeConcatenate(rngIn As Range, _
        Optional strDelimiter As String = ", ")
    [COLOR=#4E9A06]' Usage
    '  A1 = Red
    '  A2 = White
    '  A3 = Blue
    ' =RangeConcatenate(A1:A3) = Red, White, Blue
    ' =RangeConcatenate(A1:A3," - ") = Red - White - Blue[/color]
    
    Dim rngTemp As Range              [COLOR=#4E9A06]'each cell[/color]
    Dim strTemp As String
    Application.Volatile              [COLOR=#4E9A06]'autoupdates[/color]
    For Each rngTemp In rngIn         [COLOR=#4E9A06]'loop through each cell[/color]
        If Len(rngTemp.Text) > 0 Then [COLOR=#4E9A06]'ignore blank cells[/color]
            strTemp = strTemp & rngTemp & strDelimiter
        End If
    Next
    If Len(strDelimiter) > 0 Then     [COLOR=#4E9A06]'remove the final delimiter[/color]
        strTemp = Left(strTemp, Len(strTemp) - Len(strDelimiter))
    End If
    RangeConcatenate = strTemp
End Function

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[JVFriederick]

A formula only technique would be:
Assume your 3 values are in A2,A3, A4

In cell B2, enter the following formula

=B1&"'"&A2&"', "

Copy this formula down for all desired records, don't forget to remove final delimiter on last row.

You may experience problems trying to concatenate 5000 cells, it may exceed XL limits for cell contents.

JVF