CODEWITHSUNDEEP
vba
LucaS
LucaS

Reputation: 287

How to replace only certain spaces in a string?

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

Answers (3)

Spencer Barnes
Spencer Barnes

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

jamheadart
jamheadart

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

jamheadart
jamheadart

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

Related Questions