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

1. ## 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  Reply With Quote

2. ## 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?  Reply With Quote

3. ## Re: Extracting the number just after a text from textpad and writing in columns in excel Originally Posted by xsdip 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.  Reply With Quote

4. ## 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  Reply With Quote

5. ## Re: Extracting the number just after a text from textpad and writing in columns in excel Originally Posted by xsdip 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  Reply With Quote

6. ## Re: Extracting the number just after a text from textpad and writing in columns in excel Originally Posted by DanteAmor 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```  Reply With Quote

7. ## 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..   Reply With Quote

8. ## 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. . ..  Reply With Quote

9. ## 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.  Reply With Quote

10. ## 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  Reply With Quote

## User Tag List

#### Tags for this Thread

criteria, display, matched, textpad, vba & excel 2010 #### Posting Permissions

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