CODEWITHSUNDEEP
stringvbaexcel
Nitin_Alex
Nitin_Alex

Reputation: 3

how to extract a value after a colon that comes after a search word in excel vba

I have the below data in a single cell in excel. I need to write a VBA excel macro to extract some data from the below data.

****************** AUDIT REPORT RUN STATISTICS***********

Environment                   : PROD

Job Name                      : CLM_E_CLMHUB_GDWCLM_4017_D_MAIG

Server Name                   : etlprod-grid.ent.rt.csaa.com

Server IP Address             : 172.26.137.11

Source Success Count          ( s_m_extract_maig_req / V_GW_INT_CLS_MAIG ) : 691

Source Reject Count           ( s_m_extract_maig_req / V_GW_INT_CLS_MAIG ) : 0

Target Success Count          ( s_m_extract_maig_req / CLM_E_CLMHUB_GDWCLM_4017_D_MAIG.xml ) : 691

Target  Reject Count          ( s_m_extract_maig_req / CLM_E_CLMHUB_GDWCLM_4017_D_MAIG.xml ) : 0

SUMMARY

Source Total Records          : 691

Target Total Records          : 691

ETL Start                     : 02/25/2015 21:30:00

ETL End                       : 02/25/2015 21:30:15

ETL Duration(HH:MI:SS)        : 00 days 00:00:15

Business Day                  : 02/25/2015 21:30:16

Run Day                       : 2015-02-25T00:00:00-07:00

Status                        : JOB_ENDED_SUCCESSFULLY

I need to fetch the values for Source Total Records by searching the same text and fetching the number that comes after the next colon and before the next new line.(i.e 691) Is there any string function that can be used to search any word(say,Source Total Records) and to find the position of the succeeding colon(:) and to extract the text to the right of the colon.

I am sorry if my question is long or vague. Any clarification needed is welcome.

Upvotes: 0

Views: 2495

Answers (2)

ttaaoossuu
ttaaoossuu

Reputation: 7884

A non-VBA solution (if your source text is in cell A1):

A2: =SEARCH(":",A1,SEARCH("Source Total Records",A1))
A3: =SEARCH(CHAR(10),A1,A2)
A4: =VALUE(MID(A1,A2+2,A3-A2-2))

Upvotes: 1

leekaiinthesky
leekaiinthesky

Reputation: 5593

Something like this should work for you:

Sub getSourceTotalRecords()
    target = "Source Total Records"

    inputData = Cells(1, 1) 'or wherever your text is
    dataLines = Split(inputData, vbNewLine)

    For Each l In dataLines
        If Left(l, Len(target)) = target Then
            colonLocation = InStr(l, ":")
            MsgBox Mid(l, colonLocation + 1)
            Exit For 'if you're not looking for multiple hits
        End If
    Next l
End Sub

This will MsgBox the value. You might want to change that to do something else with it. You could adapt this to take the target as an argument as well, if you want to retrieve other data.

Upvotes: 0

Related Questions