VBA to get a number for a HTML style value without the "%" suffix

smide

Board Regular
Joined
Dec 20, 2015
Messages
152
Office Version
2007
Platform
Windows
Hello.


In cell A1 (Sheet 1) I'm receiving a huge HTML code. I need to extract somehow values for 'top' and 'left' without % suffix.


for: style="top: 54%; left: 13%; I need only 54 and 13


Results should be placed in column B for top values (B2:B300) and column C for left values (C2:C300).




example.


HTML in cell A1: style="top: 69%; left: 8%; background-color: rgb(0, 255, 255); style="top: 3%; left: 61%;....


Results after macro run (Sheet 1):


ABC
1style="top: 69%; left: 8%; background-color: rgb(0, 255, 255); style="top: 3%; left: 61%;....TopLeft
2698
3361
4........
5........

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results starting "B2".
Code:
[COLOR="Navy"]Sub[/COLOR] MG06Sep24
[COLOR="Navy"]Dim[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] a, b
txt = Replace(Range("A1"), ";", ":")
Sp = Split(txt, ":")
a = 1: b = 1
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
    [COLOR="Navy"]If[/COLOR] Sp(n) Like "*top" [COLOR="Navy"]Then[/COLOR]
        a = a + 1
        [COLOR="Navy"]With[/COLOR] Cells(a, "B")
            .Value = Trim(Sp(n + 1))
            .NumberFormat = "general"
            .Value = .Value * 100
        [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]ElseIf[/COLOR] Sp(n) Like "*left" [COLOR="Navy"]Then[/COLOR]
         b = b + 1
        [COLOR="Navy"]With[/COLOR] Cells(b, "C")
            .Value = Trim(Sp(n + 1))
            .NumberFormat = "general"
            .Value = .Value * 100
        [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

WaterGypsy

Well-known Member
Joined
Jan 15, 2010
Messages
697
You could start with something like this:

Code:
Sub Test1()
 
Dim temp
Dim ssTop As String
Dim sLeft As String
ssTop = ""
sLeft = ""
 
temp = Split(Range("A1").Value, " ")
For i = 0 To UBound(temp)
    If InStr(temp(i), "top") Then
        
        ssTop = Left(temp(i + 1), Len(temp(i + 1)) - 2)
        
    End If
    If InStr(temp(i), "left") Then
        sLeft = Left(temp(i + 1), Len(temp(i + 1)) - 2)
    End If
    
If ssTop <> "" And sLeft <> "" Then
 
    MsgBox ssTop & " " & sLeft
    ssTop = ""
    sLeft = ""
End If

 Next i
 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,064
Messages
5,466,380
Members
406,478
Latest member
Amar kumar

This Week's Hot Topics

Top