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

 

[xsdip]

Hi Rick,

0.3369E01 can be changed to 3.3369.. that would be fine .. could you please help me with it

 

[alansidman]

No comment about the Power Query Solution? Did you look or even try?

 

[Rick Rothstein]

Hi Rick,

0.3369E01 can be changed to 3.3369.. that would be fine .. could you please help me with it

The code I posted in Message #6 does this automatically.

 

[xsdip]

THank you so much .. I have tried it.. But output comes like this

123456 3.69E+00
12345678 0.333698
123 32

number is coming but not the alphabet.. E in 0.3698E01 is the power of 10.. ie 0.3698E01 is nothing but 3.698

 

[DanteAmor]

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

You can put the result you need according to your example

 

[DanteAmor]

You can put the result you need according to your example

Try again:

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
[COLOR=#0000ff]    sh1.Columns("B:B").NumberFormat = "General"[/COLOR]


    l2.Close False
    Application.ScreenUpdating = True
End Sub

 

[xsdip]

Sorry for the late reply.. yes I have tried it in excel developer mode .. but it all turned red as I dont know how to use a power query as I am new to VB.. Then I came to know that POWER QUERY is an add-in and is somewhat different .. It was a new learning for me and am learning about it.. Thank you so much..

 

[xsdip]

Yes finally ..

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


wfile = Application.GetOpenFilename()

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, "Fn")
num = Mid(c, ini, fin - ini)
sh1.Cells(j, "B").Value = num


End If
j = j + 1
End If
Next




l2.Close False
Application.ScreenUpdating = True
End Sub


Thank you so much DanteAmor. . ..

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[xsdip]

Awesome.. that is epic.. Could you please explain me the for loop you have defined especially the B column values..

Thank you in advance