+ Reply to Thread
Results 1 to 5 of 5

How to convert part of array column to text, separated by commas?

Hybrid View

  1. #1
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    83

    How to convert part of array column to text, separated by commas?

    Hello,

    I have this whole table from webpage
    Capture.PNG
    put into array
    Set tbl = bot.FindElementByClass("partoecodescontrol").AsTable
    Dim arrData()
    arrData = tbl.Data
    How to put market values to list with commas between them and paste all that to one cell in excel?
    f.e. A1 should contain text: "3523 95, 3523 AR, TD559W, FSK7366, CI-SB-2210, 3523 95, 3523 AR, JBU702"

    Table rows count may vary in other pages.
    Last edited by rcurious; 05-12-2021 at 02:55 AM.

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,542

    Re: How to convert part of array column to text, separated by commas?

    Change references where required.
    Sub Maybe()
    Dim arr1, i As Long, a As String
    arr1 = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
        For i = LBound(arr1) To UBound(arr1)
            If a = "" Then
                a = arr1(i, 2)
                    Else
                a = a & ", " & arr1(i, 2)
            End If
        Next i
    Cells(1, 4).Value = a
    End Sub

  3. #3
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: How to convert part of array column to text, separated by commas?

    Thank you.
    I also found this way:
    Dim arrData()
    arrData = tbl.Data
    
    nRows = UBound(arrData, 1)
    
    Dim arr() As Variant
    ReDim arr(1 To nRows - 1)
    
    For i = 2 To nRows
         arr(i - 1) = arrData(i, 2)
    Next i
    
    Dim joinedString As String
    joinedString = Join(arr, ", ")
    
    ThisWorkbook.Sheets("Sheet2").Range("B5").Value = joinedString

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,542

    Re: How to convert part of array column to text, separated by commas?

    Whichever code is best for you that works. Keep in mind future adaptations and maintenance.
    You can shorten your code slightly.
    Sub Maybe_So()
    Dim arrData(), arr1, i As Long
    arrData = tbl.Data
    ReDim arr1(1 To UBound(arrData) - 1)
        For i = 2 To UBound(arrData)
            arr1(i - 1) = arrData(i, 2)
        Next i
    ThisWorkbook.Sheets("Sheet2").Range("B5") = Join(arr1, ", ")
    End Sub
    Good luck

  5. #5
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: How to convert part of array column to text, separated by commas?

    Thank you. Optimized code also works. And yes, I'd better put that in separate method and comment well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-13-2018, 06:43 AM
  2. Convert Excel Column to text file seperated by commas
    By stri9504 in forum Excel General
    Replies: 9
    Last Post: 05-17-2015, 02:34 PM
  3. [SOLVED] How to shorten code into an array to create a list of items separated by commas
    By mdovey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2014, 12:23 PM
  4. [SOLVED] Extracting Text Separated by Commas
    By davidmg_13 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2013, 05:18 PM
  5. Creating a chart out of text separated by commas
    By MC UP in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-14-2012, 03:28 PM
  6. Programatically Convert comma separated 'Text to Column'
    By super_duper_guy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-01-2012, 08:04 PM
  7. Colum of emails to row of text separated by commas
    By debspecs in forum Excel General
    Replies: 1
    Last Post: 09-09-2008, 12:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1