• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data extraction

I

IKHAN

Member
hi,


Need help to get a macro to extract data from column C and paste in Column c and D


EXAMPLE :


Coloumn C has data


<html><b>04/12/13 00:44 - eocsupport:</b>
Event acknowledged, ownership assigned.
Reference ID associated with event. ID: 123456


</html>


DELETE cOLUMN c and extract only below info AND PASTE IN column C and Column D


outputrequired :


Column C : 04/12/13 00:44


Column D : 123456
 
Hi, IKHAN!


Try placing this code in any module and running it, adjusting row titles if needed:

-----

[pre]
Code: 
Option Explicit

Sub StealingAgainDataFromWebPages()
' constants
Const ksSourceColumn = "C"
Const kiSourceRow = 2
Const ksTargetColumns = "C:D"
Const ksID1Start = "<b>"
Const ksID1End = " - "
Const ksID2Start = "ID: "
Const ksID2End = "</html>"
' declarations
Dim rngI As Range, rngO As Range
Dim I As Long, J1 As Integer, J2 As Integer, A As String, D As Date, N As Long
' start
Set rngI = Columns(ksSourceColumn)
Set rngO = Columns(ksTargetColumns)
' process
With rngI
I = kiSourceRow
Do Until .Cells(I, 1).Value = ""
' date & time
A = .Cells(I, 1).Value
J1 = InStr(A, ksID1Start) + Len(ksID1Start)
J2 = InStr(A, ksID1End)
D = CDate(Mid(A, J1, J2 - J1 - 1))
rngO.Cells(I, 1).Value = D
' ID
J1 = InStr(A, ksID2Start) + Len(ksID2Start)
J2 = InStr(A, ksID2End)
N = CLng(Mid(A, J1, J2 - J1 - 1))
rngO.Cells(I, 2).Value = N
' cycle
I = I + 1
Loop
End With
' end
Set rngO = Nothing
Set rngI = Nothing
Beep
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
Hi, IKHAN!

Glad you solved it, more indeed if fine tuning it by yourself. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top