Reputation: 287
I created an import function for Data.
Sometimes the import string comes like this:
47588328 2020.06.25 11:42:13 buy 0.11 eurusd 1.12240 1.11902 0.00000
2020.06.25 14:05:04 1.11902 0.00 0.00 0.00 -35.35
and sometimes like this
45881875
2020.05.25 19:53:14
buy
0.10
eurusd
1.08975
1.08999
1.09503
2020.05.26 10:49:17
1.09503
0.00
0.00
-0.48
51.14
I split the values into an array. If the string is separated by line breaks there is no problem.
If the string comes separated with space I cant process it. I tried to replace the spaces with line breaks.
This would work but there are Date & Time parts. They have to stay together, and they do not when I replace spaces with line breaks.
I have this:
TextboxText = UserForm1.TextBox1.Text
TextboxText = Replace(TextboxText, " ", vbLf)
One of my ideas was to build a loop which goes trough all the spaces, then just except the space number 2 and 10. For example with pseudo code:
> For Each Space in TextboxText
> If Not space nr = 2 or space nr = 10 Then
> replace space
> End if
> Next
Upvotes: 0
Views: 105
Reputation: 2877
I'd say split it into an array as you have been doing (presumably using split(strText)
then run back through the array to rejoin the date with the time. Something like the below
For x = LBound(myArray) to Ubound(myArray)
'If IsDate(myArray(x)) Then - This didn't work as would erroneously return TRUE for decimals
If Format(Cdate(myArray(x)), "yyyy") <> "1899" Then
MyArray(x) = MyArray(x) & " " & MyArray(x+1) 'Join it to the value after
MyArray(x+1) = "" 'Delete the value after (time value)
End If
Next
Then anything you do thereafter with the array would just need to include e.g. If MyArray(x) <> ""
to miss out those empty values.
Upvotes: 1
Reputation: 5343
This isn't as clean as I'd originally hoped, I think there's probably a better way to do it but it does work:
Function GetSplit(s As String)
Dim x As Variant: x = Split(s, " ")
Dim r(13) As String
Dim i As Long
Dim j As Long
For i = 0 To 15
If i = 1 Or i = 9 Then
r(j) = x(i) & " " & x(i + 1)
i = i + 1
Else
r(j) = x(i)
End If
j = j + 1
Next i
GetSplit = r
End Function
It just cycles through an array split by " " and moves across values in the same position unless they were in position 1 or 9 (space 2 or 10) in which case it concats and increases the 1st array counter by 1, used like this:
Sub test()
Dim s As String: s = "47588328 2020.06.25 11:42:13 buy 0.11 eurusd 1.12240 1.11902 0.00000 2020.06.25 14:05:04 1.11902 0.00 0.00 0.00 -35.35"
Dim resultArray As Variant
resultArray = GetSplit(s)
End Sub
Of course this all only works if those strings you get come in exactly the same format each time, I hope those dates don't move about!
Edit
I tried editing this one to utilise IsDate
to make it more dynamic but IsDate
is returning true for lots of the strings in there, e.g. "0.11" is a date, and it even returned false for an actual date (probably due to regional settings)
Upvotes: 0
Reputation: 5343
This one returns a string directly without looping but it also relies on the premise that your date position will never change and a magic "<dt>
" placeholder string!
Function GetSplit2(s As String) As String
Dim x As Variant: x = Split(s, " ")
s = Replace(s, x(1) & " " & x(2), x(1) & "<dt>" & x(2))
s = Replace(s, x(9) & " " & x(10), x(9) & "<dt>" & x(10))
s = Replace(s, " ", vbCrLf)
GetSplit2 = Replace(s, "<dt>", " ")
End Function
Use like this:
Sub test()
Dim s As String: s = "47588328 2020.06.25 11:42:13 buy 0.11 eurusd 1.12240 1.11902 0.00000 2020.06.25 14:05:04 1.11902 0.00 0.00 0.00 -35.35"
MsgBox GetSplit2(s)
End Sub
Upvotes: 0