Excel VBA String Conversion with Math Properties

Question

I have a curiosity that has piqued my interest and has stumped me. I have a situation where I have a bunch of strings, some with Math symbols, and some that are just Characters. Some of these strings are very long, some are not, however, I would like to add a Chr(10) after the complete string of any string that has a Math symbol (specifically +, -) and then continue reading the string for the next Math symbol, and inserting a Chr(10) before it as well.

The end result should look something like this:

10+20+30+40 50+60+70+80 123a 123 123b 345 123c 123 123d 123 90+100+110+120 123 123 231 123

Converts to: 

10+20+30+40  
50+60+70+80 
123a 123 123b 345 123c 123 123d 123 
90+100+110+120 
123 123 231 123

Where the equations have no spaces, the plain letters (could be a mix of numbers and letters) could have spaces, but gets separated when a Math symbol is found.

I was looking online for some clues and I think I am very close to figuring it out. It seems a split, merge, or Right(), Left(), then Len() function is the answer.

 If Len(SearchString) = "+" Or Len(SearchString) = "-" Then
    SearchString = Left(SearchString, Chr(10))
 End If

This however doesn't work. I may not understand the Len, Left, and Right functions well enough but I am pretty positive that SearchString is the right course of action. It is also worth noting that the If/End If statement that I provided does function, it doesn't return any errors, but it doesn't do what I want it to.

Answer 1

InStr and InStrRev should be able to handle the splits.

Option Explicit    

Function splitString(str As String)

    Dim a As Long, b As Long

    a = InStr(1, str, Chr(32))
    b = InStr(1, str, Chr(43))

    Do While a > 0 And b > 0

        If b > a Then
            a = InStrRev(str, Chr(32), b)
        End If

        Mid(str, a, 1) = Chr(10)

        b = InStr(a, str, Chr(43))
        a = InStr(a, str, Chr(32))

    Loop

    splitString = str

End Function

Answer 2

So this is not ideal but uses a regex to do the breaking.

Option Explicit
Public Sub TEST()
    Dim tests(), i As Long
    tests = Array("10+20+30+40 50+60+70+80 123a 123 123b 345 123c 123 123d 123 90+100+110+120 123 123 231 123")
    For i = LBound(tests) To UBound(tests)
        Debug.Print ReplaceString(tests(i))
    Next
End Sub

Public Function ReplaceString(ByVal inputString As String) As String
Dim matches As Object, match As Object
  With CreateObject("VBScript.RegExp")
            .Global = True
            .MultiLine = True
            .Pattern = "(\w+\+|\-)+(\w)+"
            If .TEST(inputString) Then
              Set matches = .Execute(inputString)
              For Each match In matches
                  inputString = Replace$(Replace$(inputString, Chr$(32) & match, Chr$(10) & match), match & Chr$(32), match & Chr$(10))
              Next
              ReplaceString = Trim$(inputString)
            Else
              ReplaceString = inputString
            End If
  End With
End Function

---

1st Capturing Group (\w+\+|\-)+

\+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

1st Alternative \w+\+

\w+ matches any word character (equal to [a-zA-Z0-9_])

\+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

\+ matches the character + literally (case sensitive)

2nd Alternative \- \- matches the character - literally (case sensitive)

2nd Capturing Group (\w)+

\+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

\w matches any word character (equal to [a-zA-Z0-9_])

Try it here

Thanks to @RonRosenfeld for helping me to improve my regex.

Answer 3

Here's another way of using regular expressions. I use the .Replace method

Option Explicit
Sub splitter()
    Dim S As String, RE As Object
    Const spat As String = "((?:(?:\w+[+-])+)+\w+)(?=\s|$)|(?:^|\s)((?:\w+\s+)+\w+)(?=\s|$)"
    Dim sRes As Variant

S = "10+20+30+40 50+60+70+80 123a 123 123b 345 123c 123 123d 123 90+100+110+120 123 123 231 123"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .MultiLine = True
    .Pattern = spat
    sRes = .Replace(S, vbLf & "$1$2")
    MsgBox sRes
End With

End Sub

How to apply this will depend on your data setup; going through your original data and spitting out the results someplace.

Splitter

((?:(?:\w+[+-])+)+\w+)(?=\s|$)|(?:^|\s)((?:\w+\s+)+\w+)(?=\s|$)

Options: Case insensitive; ^$ don’t match at line breaks

• Match this alternative ((?:(?:\w+[+-])+)+\w+)(?=\s|$)
  • Match the regex below and capture its match into backreference number 1 ((?:(?:\w+[+-])+)+\w+)
    • Match the regular expression below (?:(?:\w+[+-])+)+
      • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
      • Match the regular expression below (?:\w+[+-])+
        • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
        • Match a single character that is a “word character” \w+
          • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
        • Match a single character present in the list below [+-]
          • The literal character “+” +
          • The literal character “-” -
    • Match a single character that is a “word character” \w+
      • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
  • Assert that the regex below can be matched starting at this position (positive lookahead) (?=\s|$)
    • Match this alternative \s
      • Match a single character that is a “whitespace character” \s
    • Or match this alternative $
      • Assert position at the very end of the string $
• Or match this alternative (?:^|\s)((?:\w+\s+)+\w+)(?=\s|$)
  • Match the regular expression below (?:^|\s)
    • Match this alternative ^
      • Assert position at the beginning of the string ^
    • Or match this alternative \s
      • Match a single character that is a “whitespace character” \s
  • Match the regex below and capture its match into backreference number 2 ((?:\w+\s+)+\w+)
    • Match the regular expression below (?:\w+\s+)+
      • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
      • Match a single character that is a “word character” \w+
        • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
      • Match a single character that is a “whitespace character” \s+
        • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
    • Match a single character that is a “word character” \w+
      • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
  • Assert that the regex below can be matched starting at this position (positive lookahead) (?=\s|$)
    • Match this alternative \s
      • Match a single character that is a “whitespace character” \s
    • Or match this alternative $
      • Assert position at the very end of the string $

$1$2

• Insert a line feed ␊
• Insert the text that was last matched by capturing group number 1 $1
• Insert the text that was last matched by capturing group number 2 $2

Created with RegexBuddy

Answer 4

In a rough way it does what you need.

sentence() = Split(10+20+30+40 50+60+70+80 123a 123 123b 345 123c 123 123d 123 90+100+110+120 123 123 231 123)

 For i = 0 To UBound(sentence)
  acu_str = sentence(i)
   If InStr(sentence(i), "+") = 0 And InStr(sentence(i), "-") = 0 Then
    Do While InStr(sentence(i + 1), "+") = 0 And InStr(sentence(i + 1), "-") = 0
    On Error GoTo ErrorHandling
     acu_str = acu_str + " " + sentence(i + 1)
    i = i + 1
    Loop
   ErrorHandling:
  End If
 Debug.Print acu_str & vbCr
 Next i