Extract data from cells

**kpratico** · 02-09-2012, 08:56 AM

I have a more complex scenario for extracting specific data from cells. I have a spreadsheet with over 30,000 lines. Each line has a cell similar to the attached spreadsheet A column. However for each entry in my spreadsheet the number of line items in the cell may vary, as shown in the sample doc.

I'm looking for a formula that will allow me to extract the text I've highlighted in red and present in a comma separate list in the B column.

Possible?

**mrice** · 02-09-2012, 11:13 AM

Try this user defined function.

Function GetList(InputCell As Range) As String
NewLine = True
Capture = False
CaptureString = ""
For N = 1 To Len(InputCell.Value)
    If Mid(InputCell.Value, N, 1) = "=" Then
        Capture = True
    End If
    
    If Mid(InputCell.Value, N, 1) = Chr$(10) Then
        Capture = True
        NewLine = True
    End If
    
    If Capture = True And NewLine = True Then
        If Mid(InputCell.Value, N, 1) = "," Then
            Capture = False
            CaptureString = CaptureString & ", "
            NewLine = False

        End If
    End If
    
    If Capture = True And Mid(InputCell.Value, N, 1) <> "=" And NewLine = True Then
        CaptureString = CaptureString & Mid(InputCell.Value, N, 1)
    End If

Next N

If Len(CaptureString) >= 2 Then CaptureString = Left(CaptureString, Len(CaptureString) - 2)
GetList = CaptureString

End Function

Paste this into a new module in the VBA editor (Alt F11)

Then use like =GetList(A2)