[help please!] Extract selected data from html files using keywords?

[tid]

Hi,

I would like to keep some selected data in an Excel cell that is available
between two keywords in an internet html page (to be downloaded by excel).

For instance :

Inside the html file, there is the following text :
---
Number : 1258622
Sector : electric
Net : 8.2
---

So I would like Excel to search between the keyword "Number" and "Sector"
and keep the data "1258622" in a cell.

Many thanks in advance for any help, ;-)
Tid

 

[Dave Peterson]

These lines are all in one cell?

If yes, then this worked ok for me (assumes A1 contains the text):

=--REPLACE(REPLACE(A1,SEARCH("Sector : ",A1)-1,LEN(A1),""),1,
8+SEARCH("Number : ",A1),"")

(all one cell)

(remove the leading -- if you want the number to be returned as text.)

 

[tid]

Dear Dave,

Thank you for your anwser. Alas I am afraid I was not enough clear.
I'll try to improve now :

These lines are all in one cell?

No if you are speaking of these lines because there are in random in the
html webpage but not in a excel worksheet:

"Inside the html file, there is the following text :
Number : 1258622
Sector : electric
Net : 8.2"

Any other idea ?
Thank you very much in advance,
Tid

 

[Dave Peterson]

So you have two strings that appear somewhere in the worksheet and you want to
copy them and the data between them to a new worksheet???

If yes, then this might get you started:

Option Explicit
Sub testme()

Dim topWord As String
Dim botWord As String
Dim TopCell As Range
Dim BotCell As Range
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim ok2Continue As Boolean

topWord = "Number : "
botWord = "Sector : "

Set curWks = ActiveSheet
Set newWks = Worksheets.Add

With curWks.UsedRange
Set TopCell = .Find(what:=topWord, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext)

Set BotCell = .Find(what:=botWord, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext)
End With

With curWks

ok2Continue = True
If TopCell Is Nothing Then
MsgBox topWord & " was not found"
ok2Continue = False
End If
If BotCell Is Nothing Then
MsgBox botWord & " was not found"
ok2Continue = False
End If

If ok2Continue Then
.Range(TopCell, BotCell).Copy _
Destination:=newWks.Range("a1")
End If

End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm