Excel VBA - Extract Multiple Values from a Single Cell and Associate Values in Source Cell's Row

Question

To preface this, I have very little experience in Excel VBA, but have used some VBA in Access.

I have a file which may contain multiple values in a single cell that need to be extracted out onto individual rows, and then have the data in multiple columns from the source row re-associated with the extracted values.

The multiple values in the single cell that need to be extracted are always in a uniform format. The cell may contain any number of sets of (), but the value I need to extract is always between the 2nd : and the closing ). This is the 'Identifier'.

For example:

(00050008009:STC:363711188)(00040022506:NYC:652263975)

Would need to extract these values onto individual rows:

363711188 652263975

All remaining values from the Source Row the value was extracted from then need to be re-associated with the value.

For example, my file may look like this:

Original File Format

I then need the file to appear as follows, on a new tab:

New File Format

I believe that a module making use of a loop, or multiple loops, is likely what is needed, but I have no idea of how to go about doing this in Excel. I'm open to all solutions. Any help is greatly appreciated! Thank you!

Answer 1

Without writing it for you, here are some pointers to get you started.

You'll need to loop through each cell in the column that contains the information you're looking for. For this, look into Worksheet.Range.

As you go through each cell, you'll need to examine the data that is actually entered into that cell. Using the Worksheet.Range.Value you can extract the contents of the cell.

Use excels string functions to parse the cell value into the values your looking for. Ex: InStr, InStrRev, etc... See this link for your options and usage for each function.

Finally you'll need to insert a row for each value that you find. Lookup Worksheet.Rows.Insert.

This should be the basic framework for what you need to do.

Answer 2

you may want to start with this code:

Option Explicit

Sub main()
    Dim myArr As Variant
    Dim cell As Range
    Dim iRow As Long, nArr As Long

    With Worksheets("batches").Range("A1").CurrentRegion '<--| change "batches" with your actual sheet name
        For iRow = .Rows.Count To 2 Step -1 '<--|loop through data rows backwards, not to process rows multiple times
            Set cell = .Cells(iRow, 3) '<--| 3rd column of current row is being processed
            cell = Mid(cell, 2, Len(cell) - 2) '<--|get the cell value between first and last bracket
            myArr = Split(cell, ")(") '<--|parse the resulting string with ")(" as delimiter and obtain and array
            nArr = UBound(myArr) '<--| calculate the array size
            If nArr > 0 Then '<--| if more than one element in array...
                With .Rows(iRow) '<--|... then refer to entire current row
                    .Offset(1).Resize(nArr).Insert '<--| ...insert n-1 rows...
                    .Resize(nArr + 1).Value = .Value '<--|...duplicate current row into newly inserted ones
                End With
                cell.Resize(nArr + 1).Value = Application.Transpose(myArr) '<--|fill 3rd column of current and newly inserted rows with array elements
            End If
        Next iRow

        For iRow = 2 To .Rows.Count '<--|loop through data rows
            With .Cells(iRow, 3)  '<--| 3rd column of current is being processed
                .Value = Right(.Value, Len(.Value) - InStrRev(.Value, ":")) '<--| "finish" it
            End With
        Next iRow
    End With
End Sub

As per your example, it assumes that your data start from cell "A1" and there's no blank row or column between it and the bottom-right cell of your data