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

Rick Rothstein

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

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

xsdip

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

New Member
Joined
May 21, 2019
Messages
13
Alansidman,​ thank you so much for the link .. I'll surely work on it.. :)
 
Last edited:

xsdip

New Member
Joined
May 21, 2019
Messages
13
Dante Amor, Thank you so much for the learning ... highly appreciable .. :)
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,023
Office Version
2010
Platform
Windows
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...
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
 
Last edited:

xsdip

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

Watch MrExcel Video

Forum statistics

Threads
1,101,773
Messages
5,482,830
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top