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

 

[Rick Rothstein]

I am having trouble following who all of your posts are responding to. Just out of curiosity, After deciding that the numbers can be displayed in normal number format, did the code I posted in Message #6 work for you or not? If not, in what way did it fail to do what you wanted?

 

[alansidman]

Starting point for Power Query-->https://www.poweredsolutions.co/2015/01/23/power-query/

 

[xsdip]

Sorry for the confusion Rick. I have replied for your code in #20 . Thank you so much for the help .. it would be highly appreciable if you can replay the doubt in #20
Thank you in advance

 

[xsdip]

Alansidman,​ thank you so much for the link .. I'll surely work on it..

 

[xsdip]

Dante Amor, Thank you so much for the learning ... highly appreciable ..

 

[DanteAmor]

Dante Amor, Thank you so much for the learning ... highly appreciable ..

Again with pleasure. Thanks for the feedback

 

[Rick Rothstein]

Sorry for the confusion Rick. I have replied for your code in #20 . Thank you so much for the help .. it would be highly appreciable if you can replay the doubt in #20
Thank you in advance

Here is my code with some explanatory comments which I hope will help you follow the logic behind the code...

Sub DisplayIDandTequal()
  Dim X As Long, FileNum As Long, TotalFile As String, IDs() As String
  
[B][COLOR="#008000"]  ' The following section reads the entire file into
  ' the TotalFile string variable in one fell swoop
[/COLOR][/B]  FileNum = FreeFile
  Open "C:\TEMP\DisplayIDandT.txt" For Binary As #FileNum 
    TotalFile = Space(LOF(FileNum))
    Get #FileNum , , TotalFile
  Close #FileNum 
  
[B][COLOR="#008000"]  ' This line of code creates an array using the text
  ' "DISPLAY ID" as the delimiter. This means starting
  ' with the second element of the array, each element
  ' starts with the display ID number and includes the
  ' associated time value
[/COLOR][/B]  IDs = Split(TotalFile, "DISPLAY ID")
  
[B][COLOR="#008000"]  ' This section loops through each of the elements
  ' identified above, one at a time. The Val function
  ' retrieves the number at the beginning of a text
  ' string and ignores the remainder of the text. Hence,
  ' the ID number is pulled from the beginning of the
  ' element and assigned to Column A. That element is
  ' then split on the text "T=" leaving the 2nd element
  ' starting with the time number which the Val function
  ' retrieves and assigns to Column B.
[/COLOR][/B]  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

 

[xsdip]

Hi Rick,


Thank you so much for the explanation.. I have one more doubt in that..when I have added some more words tto search using val function, I have got OVERFLOW Error.. Why is it so??