Extracting the number just after a text from textpad and writing in columns in excel
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Extracting the number just after a text from textpad and writing in columns in excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extracting the number just after a text from textpad and writing in columns in excel

    I want to extract specific numbers after some specific words from textpad and I have to write that into my excel columns


    i.e example, the textpad file is like this


    *INFO* CRITERIA is MATCHED. DISPLAY ID 123456 AND AT T=369
    MAY BE MATCHING OR MAY NOT BE
    *INFO* CRITERIA is MATCHED. DISPLAY ID 12345678 AND AT T=3698
    SEVERAL PACKAGES TO BE FOLLOWED
    WAIT UNTIL THE PROCESS FINISHES
    *INFO* CRITERIA is MATCHED. DISPLAY ID 123 AND AT T=32
    REGARDING THE TIMINGS..


    like this it goes. I want to extract ONLY the DISPLAY ID and the time(T=) and I have to put the DISPLAY ID and time in different columns in excel.


    Please help me as I am a beginner in VBA

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,860
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    Using Power Query and the following Mcode

    l
    Code:
    et
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        SplitColumnByPeriod = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
        SplitColumnByPhrase = Table.SplitColumn(SplitColumnByPeriod, "Column1.2", Splitter.SplitTextByDelimiter("AND AT", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
        FilteredRowsRemoveNulls = Table.SelectRows(SplitColumnByPhrase, each ([Column1.2.1] <> null and [Column1.2.1] <> "")),
        RemovedUneededColumns = Table.RemoveColumns(FilteredRowsRemoveNulls,{"Column1.1"}),
        RenamedColumns = Table.RenameColumns(RemovedUneededColumns,{{"Column1.2.1", "Dispaly ID"}, {"Column1.2.2", "Time"}})
    in
        RenamedColumns
    Results in the following.

    Data Range
    A
    B
    1
    Dispaly ID
    Time
    2
    DISPLAY ID 123456
    T=369
    3
    DISPLAY ID 12345678
    T=3698
    4
    DISPLAY ID 123
    T=32
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,294
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    Try this.

    Change data in red for your information.


    Code:
    Sub Extracting_number()
        Dim l1 As Workbook, l2 As Workbook
        Dim sh1 As Worksheet, sh2 As Worksheet
        Dim wPath As String, wFila As String
        Dim j As Long, c As Range, ini As Long, fin As Long
        Dim dId As String, num As String
        
        Set l1 = ThisWorkbook
        Set sh1 = l1.Sheets("Sheet1")
        
        Application.ScreenUpdating = False
        
        wPath = l1.Path & "\"
        wfile = "pad.txt"
        
        Workbooks.OpenText Filename:=wPath & wfile, Origin:=xlMSDOS, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
            TrailingMinusNumbers:=True
            
        Set l2 = ActiveWorkbook
        Set sh2 = l2.Sheets(1)
        
        j = 2
        For Each c In sh2.Range("A1", sh2.Range("A" & Rows.Count).End(xlUp))
            If InStr(1, c.Value, "DISPLAY ID") > 0 Then
                ini = InStr(1, c.Value, "DISPLAY ID")
                fin = InStr(ini, c.Value, "AND")
                dId = Mid(c, ini + 10, fin - ini - 10 - 1)
                sh1.Cells(j, "A").Value = dId
            
                If InStr(1, c.Value, "=") > 0 Then
                    ini = InStr(1, c.Value, "=") + 1
                    fin = InStr(ini, c.Value, "AND")
                    num = Mid(c, ini)
                    sh1.Cells(j, "B").Value = num
                End If
                j = j + 1
            End If
        Next
        
        l2.Close False
        Application.ScreenUpdating = True
    End Sub
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    Thank you so much for the reply DanteAmor .. your code really works perfectly. Can I ask one more doubt. How can we change the code if there are words after "AT T=" ? i.e. and what if the text after "AT T=" is a combination of alphabets and numbers

    *INFO* CRITERIA is MATCHED. DISPLAY ID 123456 AND AT T=0.3369E01 and Fn= 1344E01
    MAY BE MATCHING OR MAY NOT BE
    *INFO* CRITERIA is MATCHED. DISPLAY ID 12345678 AND AT T=0.3698E02 Fn = 12533
    SEVERAL PACKAGES TO BE FOLLOWED
    WAIT UNTIL THE PROCESS FINISHES
    *INFO* CRITERIA is MATCHED. DISPLAY ID 123 AND AT T=1.132 ghh = 124
    REGARDING THE TIMINGS..

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,294
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    Quote Originally Posted by xsdip View Post
    Thank you so much for the reply DanteAmor .. your code really works perfectly. Can I ask one more doubt. How can we change the code if there are words after "AT T=" ? i.e. and what if the text after "AT T=" is a combination of alphabets and numbers

    *INFO* CRITERIA is MATCHED. DISPLAY ID 123456 AND AT T=0.3369E01 and Fn= 1344E01
    MAY BE MATCHING OR MAY NOT BE
    *INFO* CRITERIA is MATCHED. DISPLAY ID 12345678 AND AT T=0.3698E02 Fn = 12533
    SEVERAL PACKAGES TO BE FOLLOWED
    WAIT UNTIL THE PROCESS FINISHES
    *INFO* CRITERIA is MATCHED. DISPLAY ID 123 AND AT T=1.132 ghh = 124
    REGARDING THE TIMINGS..

    From this text, what is the result you want
    *INFO* CRITERIA is MATCHED. DISPLAY ID 123456 AND AT T=0.3369E01 and Fn= 1344E01
    Regards Dante Amor

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,974
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    Here is another macro that you can consider. Change the red highlighted path and filename to your actual text file's path and filename... output is to the active sheet starting at cell A1.
    Code:
    Sub DisplayIDandTequal()
      Dim X As Long, FileNum As Long, TotalFile As String, IDs() As String
      FileNum = FreeFile
      Open "C:\TEMP\DisplayIDandT.txt" For Binary As #FileNum 
        TotalFile = Space(LOF(FileNum))
        Get #FileNum , , TotalFile
      Close #FileNum
      IDs = Split(TotalFile, "DISPLAY ID")
      For X = 1 To UBound(IDs)
        Cells(X, "A").Value = Val(IDs(X))
        Cells(X, "B").Value = Val(Split(IDs(X), "T=")(1))
      Next
    End Sub
    Last edited by Rick Rothstein; May 22nd, 2019 at 03:14 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,294
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    Use this

    Code:
    Sub Extracting_number()
        Dim l1 As Workbook, l2 As Workbook
        Dim sh1 As Worksheet, sh2 As Worksheet
        Dim wPath As String, wFila As String
        Dim j As Long, c As Range, ini As Long, fin As Long
        Dim dId As String, num As String, nums As Variant
        
        Set l1 = ThisWorkbook
        Set sh1 = l1.Sheets("Sheet1")
        
        Application.ScreenUpdating = False
        
        wPath = l1.Path & "\"
        wfile = "pad.txt"
        
        Workbooks.OpenText Filename:=wPath & wfile, Origin:=xlMSDOS, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
            TrailingMinusNumbers:=True
            
        Set l2 = ActiveWorkbook
        Set sh2 = l2.Sheets(1)
        
        j = 2
        For Each c In sh2.Range("A1", sh2.Range("A" & Rows.Count).End(xlUp))
            If InStr(1, c.Value, "DISPLAY ID") > 0 Then
                ini = InStr(1, c.Value, "DISPLAY ID")
                fin = InStr(ini, c.Value, "AND")
                dId = Mid(c, ini + 10, fin - ini - 10 - 1)
                sh1.Cells(j, "A").Value = dId
            
                If InStr(1, c.Value, "=") > 0 Then
                    ini = InStr(1, c.Value, "=") + 1
                    fin = InStr(ini, c.Value, "AND")
                    num = Mid(c, ini)
                    nums = Split(num, " ")
                    sh1.Cells(j, "B").Value = nums(0)
                End If
                j = j + 1
            End If
        Next
        
        l2.Close False
        Application.ScreenUpdating = True
    End Sub
    Regards Dante Amor

  8. #8
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    DanteAmor,

    The result in excel should be like this


    123456 0.3369E01
    12345678
    0.3698E02
    123
    1.132

    i.e. I need Display ID and the Time alone..

    Thanks in advance

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,974
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    Quote Originally Posted by xsdip View Post
    DanteAmor,

    The result in excel should be like this


    123456 0.3369E01
    12345678
    0.3698E02
    123
    1.132
    Do you really want the "time" displayed like, for example, 0.3369E01 or is converting it to the "normal" number 3.3369 acceptable?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting the number just after a text from textpad and writing in columns in excel

    Thank you so much Rick.. Could you please help me with this. if the value after "AT T=" is a combination of number and alphabet and if it contains decimal also, how can I change the code.. the INPUT from textpad is like this.

    INFO CRITERIA is MATCHED. DISPLAY ID 123456 AND AT T= 0.369e01 Fn
    MAY BE MATCHING OR MAY NOT BE
    INFO CRITERIA is MATCHED. DISPLAY ID 12345678 AND AT T= 0.333698 Fn
    SEVERAL PACKAGES TO BE FOLLOWED
    WAIT UNTIL THE PROCESS FINISHES
    INFO CRITERIA is MATCHED. DISPLAY ID 123 AND AT T= 32 Fn
    REGARDING THE TIMINGS..


    Output in excel should be

    123456 0.3369E01
    12345678
    0.3698E02
    123
    1.132

    The display ID and the time should be in different columns

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •