Help need to extract a part of string - InstrRev

**ragavendraph** · 02-10-2012, 07:46 AM

Hi Guys,

Can some one help me out with this

Cells(1,1) = <td><a href="/p/D2342P-LG-23in-LED-3D-widescreen-moni-HP-distributor-products/5308053.aspx">D2342P</a></td>

I was to cut and retrieve only this data "a href="/p/D2342P-LG-23in-LED-3D-widescreen-moni-HP-distributor-products/5308053.aspx"


Sub test()
Dim Pos1, Pos2 As Long
Dim sDocHTML, URL_confirm As String

sDocHTML = Cells(1, 1).Value

URL_confirm = ">" & "D2342P" & "<"
URL2 = "<a href=/p"
Pos1 = InStr(1, sDocHTML, URL_confirm)
Pos2 = InStrRev(sDocHTML, URL2, Pos1, vbTextCompare)

MsgBox (Pos1 & ":" & Pos2)


End Sub

My Pos2 variable is giving me a value 0

Can some one help me out

With regards,
Ragav.

**Kyle123** · 02-10-2012, 07:49 AM

This is perhaps a little easier:

Sub test()
Const strr$ = "<td><a href=""/p/D2342P-LG-23in-LED-3D-widescreen-moni-HP-distributor-products/5308053.aspx"">D2342P</a></td>"
MsgBox Split(Split(strr, "><a ")(1), """>")(0)
End Sub

**Kyle123** · 02-10-2012, 07:51 AM

Or as a function:

Public Function GetLink(link as string) as string
   GetLink = Split(Split(link, "><a ")(1), """>")(0)
End Function

**snb** · 02-10-2012, 08:15 AM

in this case

msgbox split(split(cells(1))(1),">")(0)

**ragavendraph** · 02-10-2012, 08:31 AM

Actually, i want to extract this data "/p/D2342P-LG-23in-LED-3D-widescreen-moni-HP-distributor-products/5308053.aspx" from an HTML page which i am opening using .Navigate.

Once opened i am searching for this pattern >D2342P< and have to get the just the Href link just before this pattern. So that is why i am using InStrRev() to search the position of the string from right to left.

Once i get the position using InStrRev(), i can cut out the required value that is "/p/D2342P-LG-23in-LED-3D-widescreen-moni-HP-distributor-products/5308053.aspx"

is there a way i can get this done using InstrRev()

**ragavendraph** · 02-10-2012, 08:45 AM

I got it guys,

My 'stringmatch' in instrRev() was the problem.... i.e is URL2 = "<a href=/p" and i changed it. and i got the output that is required "/p/D2342P-LG-23in-LED-3D-widescreen-moni-HP-distributor-products/5308053.aspx"

Sub test()
Dim Pos1, Pos2 As Long
Dim sDocHTML, URL_confirm, Final_URL As String

sDocHTML = Cells(1, 1).Value

URL_confirm = ">" & "D2342P" & "<"
URL2 = "="
Pos1 = InStr(1, sDocHTML, URL_confirm)
Pos2 = InStrRev(sDocHTML, URL2, Pos1, vbTextCompare)

MsgBox (Pos1 & ":" & Pos2)

Final_URL = "http://www.stockinthechannel.com" & Mid(sDocHTML, Pos2 + 2, (Pos1 - Pos2))

MsgBox (Final_URL)
End Sub

Its a silly mistake.... But thanks for you kind responses..

cheers
ragav