Placing brackets around numbers in a string

Question

I need to add brackets around the numbers in a string found in cells on my Excel worksheet.

For example, say I am given:

913/(300+525)

I need to get this in return:

[913]/([300]+[525])

The equations are fairly simple, should only have to deal with + - * / ( ) characters.

I attempted looping through the string character by character using the MID function but I can't get the loop(s) working correctly and end up getting a jumbled mess of random brackets and numbers back. I also considered using regular expressions but I've never used them before and have no idea if this would be a good application.

Please let me know if you need anything else. Thank you for your time!

They can be decently long. Here is another example:

I have:

(544+(1667+1668+1669+1670+1671+1672+1673)-1674)

But I need:

([544]+([1667]+[1668]+[1669]+[1670]+[1671]+[1672]+[1673])-[1674])

If you are interested in trying regex, here is a [helpful link](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops/22542835#22542835) that provides several examples of using regex a variety of ways in Excel. — Automate This, Oct 30 '14 at 20:14

score 3 · Accepted Answer · answered Oct 30 '14 at 20:08

I just threw this together but it should work

Function generateBrackets(Equation As String) As String

Dim temp As String
Dim brackets As Boolean
Dim x 'If we're using Option Explicit, or just to be safe

For x = 1 To Len(Equation)
    If Not IsNumeric(Mid(Equation, x, 1)) And brackets = False Then
        temp = temp & Mid(Equation, x, 1)
    ElseIf Not IsNumeric(Mid(Equation, x, 1)) And brackets = True Then
        temp = temp & "]" & Mid(Equation, x, 1)
        brackets = False
    ElseIf IsNumeric(Mid(Equation, x, 1)) And brackets = False Then
        temp = temp & "[" & Mid(Equation, x, 1)
        brackets = True
    ElseIf IsNumeric(Mid(Equation, x, 1)) And brackets = True Then
        temp = temp & Mid(Equation, x, 1)
    End If
Next x

generateBrackets = temp

End Function

enter image description here

score 2 · Answer 2 · answered Oct 30 '14 at 22:45

Here is a way which caters for Decimal numbers.

'~~> Add here whatever operators your equation
'~~> is likely to have
Const delim As String = "+()-/"

Sub Sample()
    Dim MyAr
    Dim sSamp As String

    sSamp = "(5.44+(16.67+1668+1669+1670+1671+1672+1673)-1674)"

    MyAr = Split(GetNewString(sSamp))

    For i = 0 To UBound(MyAr)
        sSamp = Replace(sSamp, MyAr(i), "[" & MyAr(i) & "]")
    Next i

    Debug.Print sSamp
End Sub

Function GetNewString(s As String) As String
    Dim sTemp As String

    sTemp = s

    For i = 1 To Len(delim)
        sTemp = Replace(sTemp, Mid(delim, i, 1), " ")
    Next i

    Do While InStr(1, sTemp, "  ")
        sTemp = Replace(sTemp, "  ", " ")
    Loop

    GetNewString = Trim(sTemp)
End Function

Input

"(5.44+(16.67+1668+1669+1670+1671+1672+1673)-1674)"

Output

([5.44]+([16.67]+[1668]+[1669]+[1670]+[1671]+[1672]+[1673])-[1674])

Thanks for posting this too! Decimals would need to be handled differently :) — user1274820, Oct 31 '14 at 01:02

Placing brackets around numbers in a string

2 Answers2