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

[xsdip]

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

 

[alansidman]

Using Power Query and the following Mcode

l

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
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Dispaly ID​

[/td][td]

Time​

[/td][/tr]


[tr][td]

2​

[/td][td]

DISPLAY ID 123456​

[/td][td]

T=369​

[/td][/tr]


[tr][td]

3​

[/td][td]

DISPLAY ID 12345678​

[/td][td]

T=3698​

[/td][/tr]


[tr][td]

4​

[/td][td]

DISPLAY ID 123​

[/td][td]

T=32​

[/td][/tr]
[/table]

 

[DanteAmor]

Try this.

Change data in red for your information.

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("[COLOR=#ff0000]Sheet1[/COLOR]")
    
    Application.ScreenUpdating = False
    
    wPath = l1.Path & "\"
    wfile = "[COLOR=#ff0000]pad.txt[/COLOR]"
    
    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

 

[xsdip]

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..

 

[DanteAmor]

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

 

[Rick Rothstein]

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.

Sub DisplayIDandTequal()
  Dim X As Long, FileNum As Long, TotalFile As String, IDs() As String
  FileNum = FreeFile
  Open "[B][COLOR="#FF0000"]C:\TEMP\DisplayIDandT.txt[/COLOR][/B]" 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

 

[DanteAmor]

Use this

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

 

[xsdip]

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

 

[Rick Rothstein]

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?

 

[xsdip]

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