Remove number characters appearing before a bracket in a string

user_19034667 0
Viewed 76

I have a column in an excel spreadsheet containing string variable that have numbered lists in each cell. For example one cell could contain: "1)orange 2)blue 3)white 4)purple"

I need to remove the numbers before the bracket and not sure how to tackle this with VBA

The result I need is: ")orange )blue )white )purple

TIA

vba excel
2 Answers

First enable the regex library

Tools Menu > References > Microsoft VBScript Regular Expressions

Sub demo()
  Dim input_string as String
  input_string = "1)orange 2)blue 3)white 4)purple"

  Dim regex As Object
  Set regex = New RegExp
 
  ' pattern will match one or more digits followed by a closing bracket (see https://regexr.com/)
  regex.Pattern = "\d+\)"
  ' Global is set to true to replace all instances of the pattern that are found
  regex.Global = True

  result = regex.Replace(input_string , ")")

  Debug.Print result
End Sub
user_17823700

if the list items are separated by the same character (space?), we can benefit from Split:

Function RemoveNumbers(ByVal Text As String) As String
Dim Items  As Variant, i%
Const sep = ")"
    Items = Split(Text)
    For i = LBound(Items) To UBound(Items)
        Items(i) = sep & Split(Items(i), sep, 2)(1)
    Next i
    RemoveNumbers = Join(Items)
End Function
user_149096210
Related