Split string of digits into individual cells, including digits within parentheses/brackets

0
votes

I have a column where each cell has a string of digits, ?, -, and digits in parentheses/brackets/curly brackets. A good example would be something like the following:

3????0{1012}?121-2[101]--01221111(01)1

How do I separate the string into different cells by characters, where a 'character' in this case refers to any number, ?, -, and value within the parentheses/brackets/curly brackets (including said parentheses/brackets/curly brackets)?

In essence, the string above would turn into the following (spaced apart to denote a separate cell):

3 ? ? ? ? 0 {1012} ? 1 2 1 - 2 [101] - - 0 1 2 2 1 1 1 1 (01) 1

The amount of numbers within the parentheses/brackets/curly brackets vary. There are no letters in any of the strings.

3
regexstringexcelexcel-formulavba
Not sure if this is the most efficient, but test every new character (using Mid or Left) for whether it's a {, [, or (, then activate a boolean when it finds one that prevents going to the next cell until it finds }, ], or ). – puzzlepiece87
I had been trying what puzzlepiece87 was mentioning I should do and tigeravatar solved my problem. See my comment below for a bit more info on why I chose that answer as the best. – rarivero

3 Answers

1
votes

Here you are!

RegEx method:

Sub Test_RegEx()
    Dim s, col, m
    s = "3????0{1012}?121-2[101]--01221111(01)1"
    Set col = CreateObject("Scripting.Dictionary")
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(?:\d|-|\?|\(\d+\)|\[\d+\]|\{\d+\})"
        For Each m In .Execute(s)
            col(col.Count) = m
        Next
    End With
    MsgBox Join(col.items) ' 3 ? ? ? ? 0 {1012} ? 1 2 1 - 2 [101] - - 0 1 2 2 1 1 1 1 (01) 1
End Sub

Loop method:

Sub Test_Loop()
    Dim s, col, q, t, k, i
    s = "3????0{1012}?121-2[101]--01221111(01)1"
    Set col = CreateObject("Scripting.Dictionary")
    q = "_"
    t = True
    k = 0
    For i = 1 To Len(s)
        t = (t Or InStr(1, ")]}", q) > 0) And InStr(1, "([{", q) = 0
        q = Mid(s, i, 1)
        If t Then k = k + 1
        col(k) = col(k) & q
    Next
    MsgBox Join(col.items) ' 3 ? ? ? ? 0 {1012} ? 1 2 1 - 2 [101] - - 0 1 2 2 1 1 1 1 (01) 1
End Sub
1
votes

Something else to look at :)

Sub test()

    'String to parse through
    Dim aStr As String
    'final string to print
    Dim finalString As String

    aStr = "3????0{1012}?121-2[101]--01221111(01)1"

    'Loop through string
    For i = 1 To Len(aStr)
        'The character to look at
        char = Mid(aStr, i, 1)
        'Check if the character is an opening brace, curly brace, or parenthesis
        Dim result As String
        Select Case char

            Case "["
                result = loop_until_end(Mid(aStr, i + 1), "]")
                i = i + Len(result)
                result = char & result

            Case "("
                result = loop_until_end(Mid(aStr, i + 1), ")")
                i = i + Len(result)
                result = char & result

            Case "{"
                result = loop_until_end(Mid(aStr, i + 1), "}")
                i = i + Len(result)
                result = char & result

            Case Else
                result = Mid(aStr, i, 1)

        End Select
        finalString = finalString & result & " "
    Next
    Debug.Print (finalString)
End Sub
'Loops through and concatenate to a final string until the end_char is found
'Returns a substring starting from the character after
Function loop_until_end(aStr, end_char)
    idx = 1
    If (Len(aStr) <= 1) Then
        loop_until_end = aStr
    Else
        char = Mid(aStr, idx, 1)
        Do Until (char = end_char)
            idx = idx + 1
            char = Mid(aStr, idx, 1)
        Loop
    End If
    loop_until_end = Mid(aStr, 1, idx)
End Function
0
votes

Assuming the data is in column A starting in row 1 and that you want the results start in column B and going right for each row of data in column A, here is alternate method using only worksheet formulas.

In cell B1 use this formula:

=IF(OR(LEFT(A1,1)={"(","[","{"}),LEFT(A1,MIN(FIND({")","]","}"},A1&")]}"))),IFERROR(--LEFT(A1,1),LEFT(A1,1)))

In cell C1 use this formula:

=IF(OR(MID($A1,SUMPRODUCT(LEN($B1:B1))+1,1)={"(","[","{"}),MID($A1,SUMPRODUCT(LEN($B1:B1))+1,MIN(FIND({")","]","}"},$A1&")]}",SUMPRODUCT(LEN($B1:B1))+1))-SUMPRODUCT(LEN($B1:B1))),IFERROR(--MID($A1,SUMPRODUCT(LEN($B1:B1))+1,1),MID($A1,SUMPRODUCT(LEN($B1:B1))+1,1)))

Copy the C1 formula right until it starts giving you blanks (there are no more items left to split out from the string in the A cell). In your example, need to copy it right to column AA. Then you can copy the formulas down for the rest of your Column A data.