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

xsdip

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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​
 
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top