删除字符串中括号前出现的数字字符

Question

我在excel电子表格中有一个列，其中包含每个单元格中有编号列表的字符串变量。例如，一个单元格可以包含:"1)橙色2)蓝色3)白色4)紫色

我需要删除括号前的数字，不知道如何解决这个问题与VBA

我需要的结果是:")橙色)蓝色)白色)紫色

TIA

Answer 1

首先启用正则表达式库

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

Answer 2

如果列表项由相同的字符(空格?)分隔，我们可以受益于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