CODEWITHSUNDEEP
regexexcelvba
user1077685
user1077685

Reputation:

Excel VBA Regular Expression to Remove Leading Number, Period, and Space?

I have a sheet that I use an Excel VBA macro to automatically fill all worksheet page headers (NOT column headers) with relevant information on a button click. This macro uses wSheet.Name to populate the header title. However, wSheet.Name often contains leading numbers, periods, and spaces that I don't want to appear in the header.

Note the following sample worksheet names:

Cover Page
1a. Test Page
1b. Sample Page
2. Another Test Page
3. Yet Another Test Page
4. Almost the Last Example Page
998.  Last Example Page

I would like to remove these leading numbers, periods, and spaces using a regular expression, yet I'm unsure how to code it using VBA in Excel. I would like it to be as flexible as possible. Here is an example of how I would like the worksheet names to appear:

Cover Page
Test Page
Sample Page
Another Test Page
Yet Another Test Page
Almost the Last Example Page
Last Example Page

Here is my existing code that populates the headers:

Sub FillHeaders()
'
' Auto_Fill_Project_Name Macro
'
For Each wSheet In ActiveWorkbook.Worksheets
    If wSheet.Name <> "Cover Page" Then
        wSheet.PageSetup.CenterHeader = _
            "&16&KFF0000" & ActiveSheet.Range("J1") & "&10&K000000 &16" & " " & _
            wSheet.Name & Chr(13) & "&10 &11 Revision Date: "
    End If
    Next wSheet
'
End Sub

How can I modify this to accomplish my goal?

Thank you!

EDIT -- I have taken the following approach:

Function remleaddig(str As String)
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Pattern = "^\S*\."
    str = regEx.Replace(str, "")
    remleaddig = Trim(str)
End Function

Upvotes: 1

Views: 10246

Answers (3)

brettdj
brettdj

Reputation: 55682

Update: -- Added UDF

Function StripChars(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
 With objRegex
 .Pattern = "^.+\.\s+"
.ignorecase = True
StripChars = .Replace(strIn, vbNullString)
End With
End Function

Initial Post`

I would combine the with a variant array to reduce code runtime. The code below uses

  1. Late bindging to set up the regexp
  2. The values in column A of the ActiveSheet are read into a variable array X (Note that the code will handle a 2D range, ie you could use X = Range([a1], Cells(Rows.Count, "B").End(xlUp)).Value2 to work on column A and B
  3. The regexp strips out the unwanted characters - it will handle more than one space if it is presnt before the text you want to retain
  4. The cleaned text is dumped to the active sheet starting in C1. Change this line to move the dump - you could make it dump back over the orginal values starting in A1. [c1].Resize(UBound(X, 1), UBound(X, 2)).Value2 = X

enter image description here

Code

Sub QuickUpdate()
Dim X()
Dim objRegex As Object
Dim lngRow As Long
Dim lngCol As Long

X = Range([a1], Cells(Rows.Count, "A").End(xlUp)).Value2
Set objRegex = CreateObject("vbscript.regexp")
 With objRegex
 .Pattern = "^.+\.\s+"
.ignorecase = True
For lngRow = 1 To UBound(X, 1)
For lngCol = 1 To UBound(X, 2)
X(lngRow, lngCol) = .Replace(X(lngRow, lngCol), vbNullString)
Next lngCol
Next lngRow
End With

[c1].Resize(UBound(X, 1), UBound(X, 2)).Value2 = X

End Sub

Upvotes: 2

Brad
Brad

Reputation: 12255

I'll elaborate on my comment.

This required a reference to Microsoft VBScript Regular Expressions 5.5 (or whatever the number is on your machine)

Dim r As RegExp
Set r = New RegExp
r.Pattern = "^\S*\. "

Dim c As Range, rng As Range
Set rng = Range("A1:A7")   ' <-- Set this to your range
For Each c In rng
    c.Value = r.Replace(c.Value, "")
Next c

If all that text is in one cell then change it to 

Dim r As RegExp
Set r = New RegExp
r.Pattern = "^\S*\. "
r.MultiLine = True
r.Global = True

Dim c As Range
Set c = Range("J1")
    c.Value = r.Replace(c.Value, "") '<--or place the result where ever you want

The regex explained

  • "^ This says that the pattern specified must start at the beginning of the string. we don't want to match numbers that exist in the middle of your header title.
  • \S This matches everything except white space ([^a-zA-Z0-9]). i.e. it will match letters and numbers.
  • * This matches 0 or more of the previous pattern
  • \. This matches a period (period must be escaped with a \ because the . itself means it wants to match 1 or more of the previous pattern.
  • " This matches a single space.

Upvotes: 1

A. Webb
A. Webb

Reputation: 26446

Well, you can actually use regular expressions via the RegEx object of VBScript. See https://stackoverflow.com/a/13041710/1756702 for an example.

Upvotes: 1

Related Questions