1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extraction of information

Discussion in 'VBA Macros' started by gknath79, Apr 16, 2018 at 1:27 PM.

  1. gknath79

    gknath79 New Member

    Messages:
    7
    Hello Excel Gurus,

    I would like to extract specific details from every cell [around 20000] in Column A using regex or any other possible/easiest way. I have attached the sample file with this message which contains 2 sheets. Sheet "Raw" contains the raw contents and the Sheet "Expected" has the example of how the extracted data should look like.

    I need to read from Sheet1 "Raw" and the extracted information should goto a new Sheet named "Expected"

    Any help would be appreciated.

    Attached Files:

  2. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    Hi !

    At least explain the algorithm (or even your logic) needed in order to
    proceed to the expected extraction and join a more relevant workbook.
    The better explanation and attachment, the better result …
  3. gknath79

    gknath79 New Member

    Messages:
    7
    @Marc L, the attached workbook in #1 contains two sheets (Raw & Expected).

    Raw:
    It contains 3 rows (in Column A), actual data is around 15000 rows in Column A. What i wanted to do is to extract information out of these cells and put the resulted [extracted] information in another Sheet.

    Condition 1:
    Basically whatever details that is present under the first DATE is the actual data that is to be extracted.

    2015-06-18 23:11:10 - Raki (Travel Details)

    Booking has been confirmed.

    Condition 2:

    In some cases, rather than the body of the message it also may contain Hello and Regards which should be omitted and only body of the message has to be extracted out of it.

    Hello <username>,

    Message copied.

    Regards,
    <username>

    Condition 3:
    In some cases, instead of Hello and Regards, there can be Hey <username> and Thanks <username> which should be omitted and whatever message is in between them has to be taken out.

    Note: All these 3 conditions are applicable for all the cells in Column A. To give you more details, please look at the second sheet in the attached workbook in #1. Let me know if something is unclear.
  4. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    As a starter, you can mod it accordingly to your needs :​
    Code (vb):
    Sub Demo0()
             Dim Rg As Range, V, R&
        For Each Rg In Worksheets(1).UsedRange.Columns(1).Cells
            For Each V In Split(Rg.Value, vbLf)
                If V > "" Then
                    If Val(V) = 0 Then
                        Select Case True
                            Case V = "Hi", V Like "Hello*", V Like "Please *", V Like "*regards*", V Like "VR"
                            Case Else
                                R = R + 1
                                Worksheets(2).Cells(R, 1).Value = V
                        End Select
                    End If
                End If
            Next
        Next
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    YasserKhalil likes this.

Share This Page