Excel column to array list

Question

I have a column in Excel with the following layout:

test.com
ba.com
test2.com

What i want to generate is a powershell array list so my result needs to be:

"test.com","ba.com", "test2.com"

I wanted to start by concattening my string at the start and end but can't seem to do this.

What i do is this in an empty column (E2 is value of my first site): ="""E2&"""

But the "-character doesn't seem to escape.

how to fix this?

VBasic2008 · Accepted Answer · 2019-01-20 00:29:54Z

2

You could use the ASCII code for a quote.

=CHAR(34)&E2&CHAR(34)

edited Jan 20, 2019 at 0:29

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

answered Jan 20, 2019 at 0:28

user10931127

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

Comments

BigBen · Accepted Answer · 2019-01-19 23:42:25Z

1

If you want to add a quote to the beginning and end, try

=""""&E2&""""

answered Jan 19, 2019 at 23:42

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Comments

VBasic2008 · Accepted Answer · 2019-01-20 00:27:57Z

'VBA PowerShell Array'

Highlights

Processes the first column only.
Ignores empty cells.
E.g. For the range A5:C17 the formula =PSA(A5:C17) processes the non-empty cells in A5:A17.

The Code

Function PSA(SourceRange As Range) As String
    ' Only for a one-column range

    Dim vnt As Variant    ' Source Range Array
    Dim strDel As String  ' PSA Delimiter
    Dim i As Long         ' Range Array Row Counter

    ' Copy first column of Source Range to Range Array.
    vnt = SourceRange(1, 1).Resize(SourceRange.Rows.Count)

    strDel = Chr(34) & "," & Chr(34)

    For i = 1 To UBound(vnt)
        If vnt(i, 1) <> "" Then
            If PSA <> "" Then
                PSA = PSA & strDel & vnt(i, 1)
              Else
                PSA = Chr(34) & vnt(i, 1)
            End If
        End If
    Next

    If PSA <> "" Then PSA = PSA & Chr(34)

End Function

Collectives™ on Stack Overflow

Excel column to array list

3 Answers 3

Comments

Comments

'VBA PowerShell Array'

Highlights

The Code

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

'VBA PowerShell Array'

Highlights

The Code

Comments

Your Answer

Sign up or log in

Post as a guest

Related