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

 

[Peter_SSs]

Thanks guys, can someone explain to me this part:

 With CreateObject("VBScript.RegExp")
    .Pattern = "(\d*\.\d{2})"
    .Global = True 

To understand this better you would need to read up on "Regular Expressions" which are all about finding patterns in strings.

The Pattern line here says to look for:
Any digit(s) (zero or more times in a row) followed by a "." followed by exactly 2 digits.
Global = True means to find as many such patterns as possible within the string.

For example, in the string below, I have highlighted in alternating red/blue each such pattern found

Dividends124.3228.1028.0028.0028.008.0028.00428.0029.50

yeah there can be many rows of this such as >300

A few hundred rows isn't very much, so the discussion in the thread about speed is probably not relevant to your problem. You can just choose the method that like the best, or feel more comfortable with if you might want to alter it later.