Excel VBA - How to split a string 2 characters after the decimal point

[Default001]

Hi Guys,

I have the following string:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dividends34.3228.1028.0028.0028.0028.0028.0028.0029.5030.50</code>And I'm looking to split it so I can get it as 34.32 , 28.10 , 28.00 etc then placed into a row in excel. My issue specifically being that I'm looking to split it 2 characters to the right of the decimal point because the table data im scrapping it from is it a dollar format for example:
Picture on website
This is what I have tried but again I have no idea how to split it based on 2 characters after the decimal point:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim eTR AsObject, cTR AsObject, a AsInteger
Set cTR = doc.getElementsByTagName("tr")
a =0

ForEach eTR In cTR
If Left(eTR.innerText,9)="Dividends"Then
TR = doc.getElementsByTagName("tr")(a).innerText
Debug.Print TR
'Debug.Print a

EndIf
a = a +1


Next

Dim s AsVariant
s =(Split(TR,".")(1))
Cells(1,2).Value = s
Cells(1,3).Value = s(1)
Cells(1,4).Value = s(2)
Cells(1,5).Value = s(3)
EndSub


</code>I have also tired getting each individual table data (as it looks on the image) with the following code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">

'get divs/share
chk =0
dividends:
Dim eTR3 AsObject, cTR3 AsObject
Set cTR3 = doc.getElementsByTagName("td")
g =0

ForEach eTR3 In cTR3
If Left(eTR3.innerText,9)="Dividends"Then
TR5 = doc.getElementsByTagName("td")(g).innerText
r =1
i = g +10
For h = g To i Step1
TR5 = doc.getElementsByTagName("td")(h).innerText
Cells(s +4, r +1).Value = TR5
r = r +1
Next h
EndIf
g = g +1
Next</code>But for some reason I kept getting random table data inputs that didnt belong to that row. As a way around it I thought of grabbing the table row and splitting the string up.
If anything is unclear above, please let me know and ill try to explain it the best I can. Unfortunately I cannot provide the website as it requires a username and password.

Thanks!
tom

 

[AlphaFrog]

Could you paste the HTML code for the whole page in a text file, upload the file to a file-share site, and post the link here?

 

[skywriter]

Here's the results of running the code on your sample data.

Sheet2

*	A	B	C	D	E	F	G	H	I	J	K

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:414.67px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Dividends34.3228.1028.0028.0028.0028.0028.0028.0029.5030.50[/TD]
[TD="align: right"]34.32[/TD]
[TD="align: right"]28.10[/TD]
[TD="align: right"]28.00[/TD]
[TD="align: right"]28.00[/TD]
[TD="align: right"]28.00[/TD]
[TD="align: right"]28.00[/TD]
[TD="align: right"]28.00[/TD]
[TD="align: right"]28.00[/TD]
[TD="align: right"]29.50[/TD]
[TD="align: right"]30.50[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Option Explicit

Sub NewTest()
Dim rCell As Range, matches As Object, x As Object
Dim regex As Object, c As Long
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .Pattern = "(\d{2}.\d{2})"
        For Each rCell In Range("A1")
            If .Test(rCell) Then
            c = 1
            Set matches = .Execute(rCell)
                For Each x In matches
                    rCell.Offset(, c).Value = x
                    c = c + 1
                Next x
            End If
        Next rCell
    End With
End Sub

 

[skywriter]

There is an error in the pattern.
Use this code.

Option Explicit

Sub NewTest()
Dim rCell As Range, matches As Object, x As Object
Dim regex As Object, c As Long
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .Pattern = "(\d{2}[.]\d{2})"
        For Each rCell In Range("A1")
            If .Test(rCell) Then
            c = 1
            Set matches = .Execute(rCell)
                For Each x In matches
                    rCell.Offset(, c).Value = x
                    c = c + 1
                Next x
            End If
        Next rCell
    End With
End Sub

 

[Peter_SSs]

@Bruce

The stated requirement was to split after 2 digits after the decimal point, there was no mention of what might be before the decimal point.
Although the OP's one example does have 2 digits of the 'next' number before each decimal point as well, that was not stated as a given.
So if the 'next' number might have more or less than 2 digits in the integer part, then your code will produce erroneous results without warning.

For example,

a) With "Dividends34.328.1028.0028.0028.0028.0028.0028.0029.5030.50", the 8.10 value is not returned in your results

b) With "Dividends124.3228.1028.0028.0028.0028.0028.0028.0029.5030.51", the 124.32 gets returned as 24.32


(I'm waiting to see if we get more examples or clarification on this issue.)

 

[skywriter]

Okay, then why this?

They should have posted a better example.

And I'm looking to split it so I can get it as 34.32 , 28.10 , 28.00

 

[Default001]

@Bruce

The stated requirement was to split after 2 digits after the decimal point, there was no mention of what might be before the decimal point.
Although the OP's one example does have 2 digits of the 'next' number before each decimal point as well, that was not stated as a given.
So if the 'next' number might have more or less than 2 digits in the integer part, then your code will produce erroneous results without warning.

For example,

a) With "Dividends34.328.1028.0028.0028.0028.0028.0028.0029.5030.50", the 8.10 value is not returned in your results

b) With "Dividends124.3228.1028.0028.0028.0028.0028.0028.0029.5030.51", the 124.32 gets returned as 24.32


(I'm waiting to see if we get more examples or clarification on this issue.)

Yeah this is correct because it is in dollar.cents format I was looking to for a way to split it 2 characters after the decimal as cents can only have 2 characters where as the dollars can be greater than 2 characters. Skywriter is correct though I should have made that more clear.

 

[skywriter]

Try this.

Option Explicit

Sub NewTest()
Dim rCell As Range, matches As Object, x As Object
Dim regex As Object, c As Long
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .Pattern = "(\d+\.\d{2})"
        For Each rCell In Range("A1")
            If .Test(rCell) Then
            c = 1
            Set matches = .Execute(rCell)
                For Each x In matches
                    rCell.Offset(, c).Value = x
                    c = c + 1
                Next x
            End If
        Next rCell
    End With
End Sub

Here's what I get with a different sample.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Dividends96434.328.1028.008528.0028.0028.0028.00	96434.32	8.10	28.00	8528.00	28.00	28.00	28.00
Sheet3

 

[Peter_SSs]

Assuming your data is in column A, starting at row 2 and that nothing is to the right of that data, try this in a copy of your workbook.

Sub GetNumbers()
  Dim r As Range
  
  With CreateObject("VBScript.RegExp")
    .Pattern = "(\d*\.\d{2})"
    .Global = True
    For Each r In Range("A2", Range("A" & Rows.Count).End(xlUp))
        r.Parse .Replace(Replace(r.Value, "Dividends", "[Dividends]"), "[$1]"), r.Offset(, 1)
    Next r
    With Range("A2").CurrentRegion
      .NumberFormat = "0.00"
      .Columns(2).Delete
    End With
End With
End Sub

Sample results:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Heading
2	Dividends34.328.1028.0028.0028.0028.0028.0028.0029.550.50	34.32	8.10	28.00	28.00	28.00	28.00	28.00	28.00	29.55	0.50
3	Dividends124.3228.1028.0028.0028.0028.0028.0028.0029.5030.51	124.32	28.10	28.00	28.00	28.00	28.00	28.00	28.00	29.50	30.51
4	Dividends34.3228.1028.0028.0028.0028.0028.0028.0029.5030.52	34.32	28.10	28.00	28.00	28.00	28.00	28.00	28.00	29.50	30.52
5	Dividends34.3228.1028.0028.0028.0028.0028.0028.0029.5030.53	34.32	28.10	28.00	28.00	28.00	28.00	28.00	28.00	29.50	30.53
6	Dividends34.3228.1028.0028.0028.0028.0028.0028.0029.5030.54	34.32	28.10	28.00	28.00	28.00	28.00	28.00	28.00	29.50	30.54
7	Dividends34.3228.1028.0028.0028.0028.0028.0028.0029.5030.55	34.32	28.10	28.00	28.00	28.00	28.00	28.00	28.00	29.50	30.55
Sheet2

 

[skywriter]

I get the same results.