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

xsdip

New Member
Joined
May 21, 2019
Messages
13
Hi Rick,

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

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,675
Office Version
2019
Platform
Windows
No comment about the Power Query Solution? Did you look or even try?
 

xsdip

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
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
[COLOR=#0000ff]    sh1.Columns("B:B").NumberFormat = "General"[/COLOR]


    l2.Close False
    Application.ScreenUpdating = True
End Sub
 

xsdip

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

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,102
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

xsdip

New Member
Joined
May 21, 2019
Messages
13
Awesome.. that is epic.. Could you please explain me the for loop you have defined especially the B column values..

Thank you in advance
 

Watch MrExcel Video

Forum statistics

Threads
1,101,943
Messages
5,483,823
Members
407,415
Latest member
Anton1999

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