问题描述:

In Excel I have 2 columns of data. One is the Value and the other is a semicolon-delimited list of key strings. I need to break open this second column and associate the values with individual keys as individual key-value pairs.

INPUT : Value1 "key1;key2"

OUTPUT

Value1 Key1

Value1 Key2

网友答案:

Try below sample code

Sub sample()
    Dim strValue As String

      s = 2
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row

        strValue = Range("B" & i).Value
        retval = Split(strValue, ";")

        For j = LBound(retval) To UBound(retval)

            Range("C" & s) = Range("A" & i).Value
            Range("D" & s) = retval(j)
            s = s + 1
        Next
    Next
End Sub

网友答案:

This puts the updated/split list on a new worksheet.

Sub SplitKeyVals()    
Dim wsMe As Worksheet: Set wsMe = ActiveSheet
Dim wsNew As Worksheet
Dim rngKeys As Range
Dim cl As Range
Dim keyVals() As String
Dim k As Variant
Dim r As Long: r = 1
Dim myDelimiter as String

Set wsNew = Worksheets.Add(Before:=ActiveSheet) '## Modify as needed ##'
Set rngKeys = wsMe.Range("A2:A10") '## Modify as needed ##'

'## We will use this delimiter value later ##'
myDelimiter = ";"

'## Iterate over each cell in rngKeys.Cells ##'
For Each cl In rngKeys.Cells
    '## Use the Split function to split a delimited string in to
    ' an array, so we can iterate over the values. '
    keyVals = Split(cl.Offset(0, 1).Value, myDelimiter)

    '## Now that the values are in array, iterate over the
    ' array items: '
    For Each k In keyVals
        '## Do something with this information ##'
        wsNew.Cells(r, 1) = cl.Value
        wsNew.Cells(r, 2) = k
        '## Increase the destination row for output ##'
        r = r + 1
    Next
Next

End Sub
相关阅读:
Top