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

xsdip

New Member
Joined
May 21, 2019
Messages
13
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,071
Office Version
2019
Platform
Windows
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​
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,822
Office Version
2007
Platform
Windows
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("[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

New Member
Joined
May 21, 2019
Messages
13
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,822
Office Version
2007
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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 "[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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,822
Office Version
2007
Platform
Windows
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
 

xsdip

New Member
Joined
May 21, 2019
Messages
13
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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

New Member
Joined
May 21, 2019
Messages
13
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
 

Forum statistics

Threads
1,081,991
Messages
5,362,595
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top