CODEWITHSUNDEEP
excelexcel-formula
user3127554
user3127554

Reputation: 577

Excel column to array list

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?

Upvotes: 1

Views: 319

Answers (3)

user10931127
user10931127

Reputation:

You could use the ASCII code for a quote.

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

Upvotes: 2

VBasic2008
VBasic2008

Reputation: 54838

'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

Upvotes: 1

BigBen
BigBen

Reputation: 50007

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

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

Upvotes: 1

Related Questions