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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,690
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
12,208
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
12,208
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
36,048
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
12,208
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
36,048
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,660
Messages
5,488,159
Members
407,628
Latest member
Faceless Judge

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top