RegEx to remove number + space from cells with carriage-return data VBA

user_1304458 0
Viewed 65

I have data in cells like

1  stuff
14 stuff2
9  stuff3
.
.
nn stufn

I need

stuff
stuff2
stuff3
.
.
stufn

I have

Sub test()
Dim r As Range

With CreateObject("VBScript.RegExp")
  .Global = True
  .Pattern = "^\S* "

  For Each r In Selection
      r.value = .Replace(r.value, "")
  Next

 End With
End Sub

But it only removes the first instance in the string

stuff
14 stuff2
9  stuff3
.
.
nn stufn

How to remove all the numbers + space

Thanks

vba excel regex
2 Answers

You need to use

.MultiLine = True
.Pattern = "^\S+ +"

The .MultiLine = True will make ^ match at the beginning of each line and + will match 1+ spaces.

user_38329700

as per your data example you could avoid regex and simply go:

Selection.Replace what:="*stuf", replacement:="stuf", lookAt:=xlPart
user_131480750
Related