CODEWITHSUNDEEP
arraysexcelvba
Andyjm
Andyjm

Reputation: 335

Convert Excel list into Array format

I have a few fairly long currency lists in excel which I need to convert into an array format, for use in a different program. Are there any built-in formula that will return the string values of a list, into a string array?

For example the following:

a
b
c
d
e

Will return as "a","b","c","d","e" (even if it's just in the formula bar so I can copy and paste!)

I would prefer not to use VBA if possible simply due to time constraints, however if that works out relatively easy, any suggestions on that would be really helpful too.

Many thanks in advance

Upvotes: 2

Views: 10691

Answers (4)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Another way to convert list into array. Hope it helps.

Public Function ToArrStr1(r As Range) As String
Dim out As String
For i = 1 To r.Cells.Count
    If out = "" Then out = """" & r.Cells(i) & """" Else out = out & ",""" & r.Cells(i) & """"
Next i
ToArrStr1 = out
End Function

Then in the sheet you can: =ToArrStr1(A1:A5)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152465

FWIW, If you have the most recent upgrade to Excel, or are using the online app, then there is a built in formula. It is TEXTJOIN(). So:

="""" & TEXTJOIN(""",""",TRUE,A1:A5) & """"

enter image description here

Upvotes: 2

Code Different
Code Different

Reputation: 93151

The way I've seen this being handled in purely Excel formula is accumulating line by line:

A    B
---  -----------------
a    =""""&A1&""""
b    =B1&","""&A2&""""
c    =B2&","""&A3&""""
d    =B3&","""&A4&""""

Upvotes: 2

Alex K.
Alex K.

Reputation: 175766

Nothing built in directly AFAIK.

Add a VBA module, paste:

Public Function ToArrStr(r As Range) As String
    ToArrStr = """" & Join(Application.Transpose(r), """,""") & """"
End Function

Then in the sheet you can: =ToArrStr(A1:A5)

(Assumes your passing > 1 cell vertically)

Upvotes: 2

Related Questions