Extracting specific text from a cell (possible multiple instances) RRS feed

  • Question

  • I am using Excel 2010 to assist with my Program and Project Management tasks. Specifically I have another product that exports a CSV and am leveraging it for my RACI (Responsibile, Accountable, Consulted, and Informed) matrix. One example of the output is the following:

    SME~R, Finance SME~C, PM~R, Rptng SME~C, zOS SME~C, PM~A, Perf SME~C

    Notice that there are two "~R"s and four "~C"s. What I am hoping to do is have four columns (R, A, C, and I) that will have the text between the ~ and the previous comma (e.g. the R cell would have "SME, PM", the A cell would have "PM", and the C cell "Finance SME, Rptng SME, zOS SME, and Perf SME". No I data for this instance.

    Thank you, Matt

    Monday, October 6, 2014 12:52 PM


  • Hi MNTechDad,

    There are probably a thousands of ways to tackle this problem. I decided to write the code for you and share the way I would have done this. 

    Say that your column X has the long strings is the format you provided in your question. 

    If you right click the worksheet tab and select VIEW CODE

    and then right click the VBA Project and Insert a Module - (should be Module1)

    and copy paste the code:

    Function ExtractPart(s As String, col As String) As String
        Dim v As Variant
        v = Split(s, ", ")
        Dim c As New Collection
        For Each i In v
            If InStr(1, i, "~" & UCase(col), vbTextCompare) Then
                c.Add Replace(i, "~" & UCase(col), vbNullString)
            End If
        ExtractPart = BuildFinal(c)
    End Function
    Private Function BuildFinal(c As Collection) As String
        Dim final As String
        If c.Count > 0 Then
            For Each i In c
                final = final & i & ", "
            BuildFinal = vbNullString
            Exit Function
        End If
        BuildFinal = Left(final, Len(final) - 2)
    End Function

    and now return back to your worksheet and type in cells;

    [A1] should have formula:


    [C1] formula should be:

    =ExtractPart(X1, "C")

    [I1] should have:


    and finally [R1]:

    =ExtractPart(X1, "R")

    NOTE: remember you should have your long string in X1

    Your spreadsheet should look like this:

    and I hope this is the result you are after :)

    Please don't forget to mark this as an answer if it has solved your problem. Thanks

    Monday, October 6, 2014 4:06 PM