Convert Excel list into Array format

Question

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

See superuser.com/questions/829686/…

aucuparia
– aucuparia

2016-03-29 12:19:19 +00:00
Commented Mar 29, 2016 at 12:19 — aucuparia
– aucuparia, Commented Mar 29, 2016 at 12:19

Alex K. · Accepted Answer · 2016-03-29 12:20:54Z

2

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)

answered Mar 29, 2016 at 12:20

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andyjm Over a year ago

This is spot on and will literally save me hours!!

Code Different · Accepted Answer · 2016-03-29 12:25:00Z

2

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&""""

answered Mar 29, 2016 at 12:25

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Comments

Scott Craner · Accepted Answer · 2016-03-29 13:23:10Z

2

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) & """"

answered Mar 29, 2016 at 13:23

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Comments

Karthick Gunasekaran · Accepted Answer · 2016-03-30 06:24:47Z

0

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)

edited Mar 30, 2016 at 6:24

answered Mar 29, 2016 at 13:04

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Collectives™ on Stack Overflow

Convert Excel list into Array format

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related