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]

I get the same results.

I'm not sure if you are suggesting that I shouldn't have offered an alternative (as it happens I hadn't seen your post as it was made while I was composing mine) or just confirming that the results are the same?

I actually disagree that the results are the same - as posted. Your code returns string values, mine returns numerical values. I have no idea whether that matters for the OP, just noting the slight difference.

 

[Peter_SSs]

@Tom

1. About how many rows of this data is there to split like this?

2. Does every row have 10 numbers to extract like your sample, or can that vary?

3. If it can vary, can you place a maximum on how many numbers need to be extracted from a single row?

 

[Rick Rothstein]

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

Peter,

You can speed up you code considerably by disabling ScreenUpdating (1.19 seconds versus 8.84 seconds for 10,000 rows of data). Here is the code I came up with (non-RegExp naturally) which comes in at 0.91 seconds for the same 10,000 rows of data...

Sub SeparateNumbersTwoAfterDecimalPoint()
  Dim r As Long, X As Long, Digits As String, Data As Variant
  Application.ScreenUpdating = False
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  For r = 1 To UBound(Data)
    For X = Len(Data(r, 1)) - 3 To 1 Step -1
      If Mid(Data(r, 1), X, 1) Like "[!0-9.]" Then
        Data(r, 1) = Mid(Data(r, 1), X + 1)
        Exit For
      ElseIf Mid(Data(r, 1), X, 1) Like "." Then
        Data(r, 1) = Application.Replace(Data(r, 1), X + 3, 0, vbTab)
      End If
    Next
  Next
  Range("B2").Resize(UBound(Data)) = Data
  Columns("B").TextToColumns , xlDelimited, , , True
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Peter,

You can speed up you code considerably by disabling ScreenUpdating

Of course.
At this point in time though we only know of one row of sample data so it didn't seem vital, but partly why I have asked the questions I did in post #12.

In any case the method I used is not as fast as the code skywriter used, but I wanted to practice the Parse method & just offer something a bit different.

If we do have large data, again why I asked my recent questions, then I would also move away from RegExp & use this method, which also produces the 2 decimal place formatting as requested and on my machine took 0.3 seconds compared to 1.2 seconds for your code for 10,000 rows.

Until I get an answer to my questions 2 & 3 I have assumed no more than 20 numbers to be extracted per row.
Also at this stage I have assumed that all rows start with "Dividends" as there has been nothing to indicate otherwise.
I still haven't bothered with ScreenUpdating as it makes virtually no discernible difference with this code that only writes data to the sheet once.

Sub BreakItUp()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, pos As Long
  Dim s As String
   
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 20)
  For i = 1 To UBound(a)
    s = Mid(a(i, 1), 10)
    j = 0
    pos = InStr(1, s, ".")
    Do Until pos = 0
      j = j + 1
      b(i, j) = Left(s, pos + 2)
      s = Mid(s, pos + 3)
      pos = InStr(1, s, ".")
    Loop
  Next i
  With Range("B2").Resize(UBound(b, 1), UBound(b, 2))
    .NumberFormat = "0.00"
    .Value = b
  End With
End Sub

 

[Rick Rothstein]

If we do have large data, again why I asked my recent questions, then I would also move away from RegExp & use this method, which also produces the 2 decimal place formatting as requested and on my machine took 0.3 seconds compared to 1.2 seconds for your code for 10,000 rows.

First off, your code runs in 0.15 seconds on my computer. I posted my code just before going to bed and as I was drifting off to sleep, I wondered how much time the TextToColumns method might be costing me... it looks like the answer is about 0.35 seconds. That still makes my code significantly slower than your code. I know I lose a little time examining each character rather than using the InStr function to jump directly between dots, but I would not think the time difference would be "gigantic". Actually, I think my reliance on Application.Replace to simplify adding a delimiter for the TextToColumns method to use is what is slowing the code down as compared to your method.

Also at this stage I have assumed that all rows start with "Dividends" as there has been nothing to indicate otherwise.

I designed my code to account for the fact that the beginning text might not always be "Dividends". It looks like the change to your code would, if you do it the way I am about to suggest, would cost another 0.02 seconds (not really all that much in the scheme of things). You could handle varying beginning text by replacing this line of code...

s = Mid(a(i, 1), 10)

with this loop...

For pos = 1 To Len(a(i, 1))
  If Mid(a(i, 1), pos, 1) Like "[0-9.]" Then
    s = Mid(a(i, 1), pos)
    Exit For
  End If
Next

I still haven't bothered with ScreenUpdating as it makes virtually no discernible difference with this code that only writes data to the sheet once.

Agreed... I also do not use it when I blast the results to the worksheet in a single operation and for that same reason. The only reason I used it in my code this time, even though I blast the main part of the data to the worksheet all at once, was to hide the view of that happening just before the TextToColumns method is applied... you could just see it flash during the that dual operation.

 

[skywriter]

I'm not sure if you are suggesting that I shouldn't have offered an alternative (as it happens I hadn't seen your post as it was made while I was composing mine) or just confirming that the results are the same?

I actually disagree that the results are the same - as posted. Your code returns string values, mine returns numerical values. I have no idea whether that matters for the OP, just noting the slight difference.

Actually I was looking for approval and/or constructive criticism from a guru on this board.

The string thing is easily fixed by cdbl, I know you know that, which makes your bringing it up seem glib.

I'm here to help and learn, I'm not a professional, I'm completely self taught.

I'm very weak in regular expressions, but I learn from doing and I was pretty proud of what I came up with all on my own.

I don't really care if it's better or worse than a guru's code, it's mine I accept it and as I keep plugging forward my code will get better.

 

[Peter_SSs]

@Bruce

Thanks for the clarification. I am also completely self-taught (or maybe I should say MrExcel-taught) so understand what you are saying. And yes, your code was fine, and as I pointed out later, was faster than mine.
I too was trying to "learn from doing" by using the Parse method which I only found from the forum some time ago (a pgc01 post) & have been waiting to find somewhere to use it.

Anyway, it's all good and the OP has plenty to choose from now.

 

[Rick Rothstein]

I too was trying to "learn from doing" by using the Parse method which I only found from the forum some time ago

I just learned a new method!

I never saw Parse before... it looks interesting and potentially useful.

Thanks for mentioning it so that I could notice it.

 

[Default001]

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

Excel tables to the web >> Excel Jeanie HTML 4

Thanks guys, can someone explain to me this part:

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

Is this the splitting of the string and the d{2} is the number of characters?

thanks
tom

 

[Default001]

@Tom

1. About how many rows of this data is there to split like this?

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

2. Does every row have 10 numbers to extract like your sample, or can that vary?

They can vary but a maximum of 10

3. If it can vary, can you place a maximum on how many numbers need to be extracted from a single row?

10 is maximum

Wow there is alot of ways of doings this. Iv learn't methods i didnt even know existed, thank you everyone.