Extracting the number just after a text from textpad and writing in columns in excel
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

Thread: Extracting the number just after a text from textpad and writing in columns in excel
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Rick,

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

  2. #12
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,860
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

    No comment about the Power Query Solution? Did you look or even try?
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #13
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,968
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by xsdip View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #14
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  5. #15
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by xsdip View Post
    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
    Regards Dante Amor

  6. #16
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by DanteAmor View Post
    You can put the result you need according to your example
    Try again:

    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
        sh1.Columns("B:B").NumberFormat = "General"
    
    
        l2.Close False
        Application.ScreenUpdating = True
    End Sub
    Regards Dante Amor

  7. #17
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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..

  8. #18
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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. . ..

  9. #19
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

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

  10. #20
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Thank you in advance

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •