Modify a "Split" statement

I had help in the past from this forum to make a split statement

                For Each Sh4Cell In Sh4Range
                    If Left(Sh4Cell, 4) = "TA(#" Then
                        sBubbleNumber = Split(Replace(Sh4Cell, "TA(#", "", 5), "-")(0)

The above takes a cell with data like this:
"TA(#31," and splits it like this:
"31"

Now I have another unique split. Using the same example above but modifying the cell data.
"TA({#31," and splits it like this: (Added a "{" )
"{31}" (adding an ending "}" )

Any hints, tips or examples are appreciated.

Hello Rick,

Here is one way using Regular Expressions. If the number is found between the (# and ), it is returned. Otherwise the cell value is returned unchanged.

Extract Number Macro

Function ExtractNumber(Cell As Range)

  Dim RegExp As Object
  
    If Cell.Count > 1 Then Exit Function
    
      Set RegExp = CreateObject("VBScript.RegExp")
      
      RegExp.Pattern = "(.*\(#)(\d+)(\).*)"
      ExtractNumber = RegExp.Replace(Cell, "$2")
      
End Function

I believe I understand what your function does but I do not think it will output what I need.
Here is the macro.

                With Sheets("Sheet4")    'Loop thru Column C
                    Sh4LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
                    Set Sh4Range = .Range("C1:C" & Sh4LastRow)
                End With

                For Each Sh4Cell In Sh4Range
                    If Left(Sh4Cell, 4) = "TA(#" Then
                        sBubbleNumber = Split(Replace(Sh4Cell, "TA(#", "", 5), "-")(0)
                        Sh4Cell.Activate
                        sType = Sh4Cell.Offset(0, -1).Value
                        Select Case sType
                        Case "ANGLE", "APEX", "ANGLE_BETWEEN", "COORDINATE", "DCOORD", _
                             "DIST_BETWEEN", "DIAMETER", "HAPEX", "RADIUS", "WIDTH", "USETOL", _
                             "IJKDST"
                            sActual = Sh4Cell.Offset(1, 2).Value
                            sActual = Replace(sActual, "ACT=", "", 5)
                            sActual = Replace(sActual, "-", "", 1)
                            'MsgBox sActual 'for testing
                            'Paste sActual in cell for sBubbleNumber
                            For Each Sh3Cell In Sh3Range
                                If Sh3Cell = sBubbleNumber Then
                                    If Sh3Cell.Offset(0, 16).Value = "" Then    '0, 12
                                        Sh3Cell.Offset(0, 16).Value = sActual    '0, 12
                                        'MsgBox ActiveCell.Address & "" & ActiveCell.Value & " " & sActual 'for testing
                                    Else
                                        'Place lowest value in left cell and highest value in right cell
                                        If Sh3Cell.Offset(0, 16).Value > sActual Then
                                            If Sh3Cell.Offset(0, 16).Value > Sh3Cell.Offset(0, 17).Value Then
                                                Sh3Cell.Offset(0, 17).Value = Sh3Cell.Offset(0, 16).Value
                                                Sh3Cell.Offset(0, 16).Value = sActual
                                            End If
                                        End If
                                        'Over write left cell if new value is less than existing value
                                        If sActual < Sh3Cell.Offset(0, 16).Value Then
                                            Sh3Cell.Offset(0, 16).Value = sActual
                                        Else
                                            'Over write right cell if new value is greater than existing value
                                            If sActual > Sh3Cell.Offset(0, 17).Value Then
                                                Sh3Cell.Offset(0, 17).Value = sActual
                                            End If
                                        End If
                                    End If
                                End If
                            Next Sh3Cell
                        Case Else
                            sActual = Sh4Cell.Offset(1, 0).Value
                            sActual = Replace(sActual, "ACT=", "", 5)
                            sActual = Replace(sActual, "-", "", 1)

                            'Paste sActual in cell for sBubbleNumber
                            For Each Sh3Cell In Sh3Range
                                If Sh3Cell = sBubbleNumber Then
                                    'MsgBox sActual & " " & Sh4Cell.Offset(1, 0).Address & _
                                     " " & Sh3Cell 'for testing
                                    If Sh3Cell.Offset(0, 16).Value = "" Then    '0, 12
                                        Sh3Cell.Offset(0, 16).Value = sActual    '0, 12
                                    Else
                                        'Place lowest value in left cell and highest value in right cell
                                        If Sh3Cell.Offset(0, 16).Value > sActual Then
                                            If Sh3Cell.Offset(0, 16).Value > Sh3Cell.Offset(0, 17).Value Then
                                                Sh3Cell.Offset(0, 17).Value = Sh3Cell.Offset(0, 16).Value
                                                Sh3Cell.Offset(0, 16).Value = sActual
                                            End If
                                        End If
                                        'Over write left cell if new value is less than existing value
                                        If sActual < Sh3Cell.Offset(0, 16).Value Then
                                            Sh3Cell.Offset(0, 16).Value = sActual
                                        Else
                                            'Over write right cell if new value is greater than existing value
                                            If sActual > Sh3Cell.Offset(0, 17).Value Then
                                                Sh3Cell.Offset(0, 17).Value = sActual
                                            End If
                                        End If
                                    End If
                                End If
                            Next Sh3Cell
                        End Select
                    End If
                Next Sh4Cell

sBubbleNumber is being compared to the value of all cells in Column A for a match, if sBubbleNumber and a cell in column A match then another cell (far right, offset 16 and or 17 cells from A) is populated.

My mission (haha) is to modify my search from the original code of these two lines:

                    If Left(Sh4Cell, 4) = "TA(#" Then
                        sBubbleNumber = Split(Replace(Sh4Cell, "TA(#", "", 5), "-")(0)

To something like this?

                    If Left(Sh4Cell, 5) = "TA({#" Then
                        sBubbleNumber = Split(Replace(Sh4Cell, "TA({#", "", 6), "-")(0)

Added opening bracket "{".
sBubbleNumber would have to be formatted as "{XX}" where XX is a numeric value.

Looking at this, I think I can do a "Replace" for the "#" with "{" and then add a closing bracket "}"?

Hello Rick,

Perhaps I misunderstood your post. You are not looking to extract the number from the string "TA({#31" as 31 but as (31)?

Hello Rick,

This version will extract the number and add the braces to around it. If not it will return the original value.

Function ExtractNumber(Cell As Range)

  Dim RegExp As Object
  
    If Cell.Count > 1 Then Exit Function
    
      Set RegExp = CreateObject("VBScript.RegExp")
      
      RegExp.Pattern = "(.*\()(\d+)(\).*)"
      
      If RegExp.Test(Cell) = True Then
         ExtractNumber = "{" & RegExp.Replace(Cell, "$2") & "}"
      Else
         ExtractNumber = RegExp.Replace(Cell, "$2")
      End If
         
      
End Function

You would call it like this ...

 Dim N As Variant

   N = ExtractNumber(Sh4Cell)
   If N <> "" Then 
      sBubbleNumber = N 
      < other code to execute >
   End If

Originally Posted by Leith Ross

Hello Rick,

Perhaps I misunderstood your post. You are not looking to extract the number from the string "TA({#31" as 31 but as (31)?

The number with opening and closing brackets.
As {31} (the squiggly brackets, I just can not remember what they are called)

Hello Rick,

In my last post, I did not have the pound sign in the expression. The code below will place the braces around the number.

Function ExtractNumber(Cell As Range)

  Dim RegExp As Object
  
    If Cell.Count > 1 Then Exit Function
    
      Set RegExp = CreateObject("VBScript.RegExp")
      
      RegExp.Pattern = "(.*\(#)(\d+)(\).*)"
      
      If RegExp.Test(Cell) = True Then
         ExtractNumber = "{" & RegExp.Replace(Cell, "$2") & "}"
      Else
         ExtractNumber = RegExp.Replace(Cell, "$2")
      End If
               
End Function

Thank you Leith, as always your solutions use far less code than mine LOL