Header
PowerPoint tips, hints and tutorials that will change your presentations for ever!

INDEX

 

Jigsaws
Sounds
Video
Custom Shows
vba code
NaviSlides
Games for teachers
Bullets
Triggers
Security
Flash Cards
Multiple Instances
PowerPoint 2007
Mail Merge
Random events
Animation
Hyperlinks
Set spellcheck language


Home buttonTutorial buttonContact buttonProducts button


Regular Expressions in PowerPoint

The first example shows how to find and replace a number with commas (European decimal) with a number with a period (UK / USA decimal.

The second example shows how to find and list consecutive CAPITAL letters with optional periods. i.e abbreviations.

Example 1

Working in Europe we often get presentations using the , as a decimal separator 234.72 for example. For UK or US use this needs to be changed to 234.72.

In Word this is possible using the advanced search options available which are based on Regular Expressions but PowerPoint only has a basic Search and replace function. Searching for commas and replacing with full stops (periods) will of course change ALL commas not just those in number.

You could create a complex macro to find strings which are numeric and only do the replace there but it would be time consuming.

Here's How to Use regX in PowerPoint

Sub use_regex() 
    Dim regX As Object 
    Dim osld As Slide 
    Dim oshp As Shape 
    Dim strInput As String 
    Dim b_found As Boolean 
    Dim iRow As Integer 
    Dim iCol As Integer 
     
    Set regX = CreateObject("vbscript.regexp") 
    With regX 
        .Global = True 
        .Pattern = "(\d+)\,(\d+)" 
    End With 
    For Each osld In ActivePresentation.Slides 
        For Each oshp In osld.Shapes 
            If oshp.HasTable Then 
                For iRow = 1 To oshp.Table.Rows.Count 
                    For iCol = 1 To oshp.Table.Columns.Count 
                        strInput = oshp.Table.Cell(iRow, iCol).Shape.TextFrame.TextRange.Text 
                        b_found = regX.Test(strInput) 
                        If b_found = True Then 
                            strInput = regX.Replace(strInput, "$1.$2") 
                            oshp.Table.Cell(iRow, iCol).Shape.TextFrame.TextRange = strInput 
                        End If 
                    Next iCol 
                Next iRow 
            Else 
                If oshp.HasTextFrame Then 
                    If oshp.TextFrame.HasText Then 
                        strInput = oshp.TextFrame.TextRange.Text 
                        b_found = regX.Test(strInput) 
                        If b_found = True Then 
                            strInput = regX.Replace(strInput, "$1.$2") 
                            oshp.TextFrame.TextRange = strInput 
                        End If 
                    End If 
                End If 
            End If 
        Next oshp 
    Next osld 
    Set regX = Nothing 
End Sub 

This second example searchs for consecutive CAPITALS with or without periods (i.e. abbreviations).

The result is opened as a printable text file.

Sub use_regexABB() 
    Dim regX As Object 
    Dim oMatch As Object 
    Dim osld As Slide 
    Dim oshp As Shape 
    Dim strInput As String 
    Dim b_found As Boolean 
    Dim strReport As String 
    Dim iRow As Integer 
    Dim iCol As Integer 
    Dim i As Integer 
    Dim GI As Long 
    Dim iFileNum As Integer 
    Dim b_First As Boolean 
    Dim b_start As Boolean 
    Dim strpattern As String 
    strpattern = "\b[A-Z(.\d)?]{2,}\b" 'CAPS with optional . or digit
    On Error Resume Next 
    Set regX = CreateObject("vbscript.regexp") 
    With regX 
        .Global = True 
        .Pattern = strpattern 
         
    End With 
    For Each osld In ActivePresentation.Slides 
        For Each oshp In osld.Shapes 
            Select Case oshp.Type 
            Case Is = 14 
                If oshp.HasTable Then 
                    For iRow = 1 To oshp.Table.Rows.Count 
                        For iCol = 1 To oshp.Table.Columns.Count 
                            strInput = oshp.Table.Cell(iRow, iCol).Shape.TextFrame.TextRange.Text 
                            b_found = regX.Test(strInput) 
                            If b_found = True Then 
                                If Err <> 0 Then 
                                    MsgBox strpattern & " is not a recognised RegEx pattern." 
                                    Exit Sub 
                                End If 
                                If Not b_start Then 
                                    b_start = True 
                                    strReport = strReport & "Results" & vbCrLf 
                                End If 
                                If Not b_First And b_start Then 
                                    strReport = strReport & vbCrLf & "In Table on Slide " & osld.SlideIndex & ": " 
                                    b_First = True 
                                Else 
                                     
                                End If 
                                Set oMatch = regX.Execute(strInput) 
                                For i = 0 To oMatch.Count - 1 
                                     
                                    strReport = strReport & oMatch(i) & " / " 
                                     
                                Next i 
                                 
                            End If 
                        Next iCol 
                    Next iRow 
                     
                End If 
                If oshp.HasTextFrame And Not oshp.HasTable Then 
                    If oshp.TextFrame.HasText Then 
                        strInput = oshp.TextFrame.TextRange.Text 
                         
                        b_found = regX.Test(strInput) 
                        If Err <> 0 Then 
                            MsgBox strpattern & " is not a recognised RegEx pattern." 
                            Exit Sub 
                        End If 
                        If b_found = True Then 
                            If Not b_start Then 
                                b_start = True 
                                strReport = strReport & "Results" & vbCrLf 
                            End If 
                            If Not b_First And b_start Then 
                                strReport = strReport & vbCrLf & "In Placeholder on Slide " & osld.SlideIndex & ": " 
                                b_First = True 
                            Else 
                                 
                            End If 
                            Set oMatch = regX.Execute(strInput) 
                            For i = 0 To oMatch.Count - 1 
                                 
                                strReport = strReport & oMatch(i) & " / " 
                                 
                            Next i 
                             
                        End If 
                    End If 
                End If 
                 
                 
            Case Is = msoSmartArt 
                For GI = 1 To oshp.GroupItems.Count 
                    If oshp.GroupItems(GI).HasTextFrame Then 
                        If oshp.GroupItems(GI).TextFrame2.HasText Then 
                            strInput = oshp.GroupItems(GI).TextFrame2.TextRange.Text 
                            b_found = regX.Test(strInput) 
                            If b_found = True Then 
                                If Err <> 0 Then 
                                    MsgBox strpattern & " is not a recognised RegEx pattern." 
                                    Exit Sub 
                                End If 
                                If Not b_start Then 
                                    b_start = True 
                                    strReport = strReport & "Results" & vbCrLf 
                                End If 
                                If Not b_First And b_start Then 
                                    strReport = strReport & vbCrLf & "In Smart Art on Slide " & osld.SlideIndex & ": " 
                                    b_First = True 
                                Else 
                                     
                                End If 
                                Set oMatch = regX.Execute(strInput) 
                                For i = 0 To oMatch.Count - 1 
                                    strReport = strReport & oMatch(i) & " / " 
                                     
                                Next i 
                                 
                            End If 
                             
                        End If 
                    End If 
                Next GI 
                 
            Case Else 
                If oshp.HasTable Then 
                    For iRow = 1 To oshp.Table.Rows.Count 
                        For iCol = 1 To oshp.Table.Columns.Count 
                            strInput = oshp.Table.Cell(iRow, iCol).Shape.TextFrame.TextRange.Text 
                            b_found = regX.Test(strInput) 
                            If b_found = True Then 
                                If Err <> 0 Then 
                                    MsgBox strpattern & " is not a recognised RegEx pattern." 
                                    Exit Sub 
                                End If 
                                If Not b_start Then 
                                    b_start = True 
                                    strReport = strReport & "Results" & vbCrLf 
                                End If 
                                If Not b_First And b_start Then 
                                    strReport = strReport & vbCrLf & "In Table on Slide " & osld.SlideIndex & ": " 
                                    b_First = True 
                                Else 
                                     
                                End If 
                                Set oMatch = regX.Execute(strInput) 
                                For i = 0 To oMatch.Count - 1 
                                     
                                    strReport = strReport & oMatch(i) & " / " 
                                     
                                Next i 
                                 
                            End If 
                        Next iCol 
                    Next iRow 
                     
                     
                End If 
                If oshp.HasTextFrame And Not oshp.HasTable Then 
                    If oshp.TextFrame.HasText Then 
                        strInput = oshp.TextFrame.TextRange.Text 
                        b_found = regX.Test(strInput) 
                        If b_found = True Then 
                            If Err <> 0 Then 
                                MsgBox strpattern & " is not a recognised RegEx pattern." 
                                Exit Sub 
                            End If 
                            If Not b_start Then 
                                b_start = True 
                                strReport = strReport & "Results" & vbCrLf 
                            End If 
                            If Not b_First And b_start Then 
                                strReport = strReport & vbCrLf & "On Slide " & osld.SlideIndex & ": " 
                            End If 
                            Set oMatch = regX.Execute(strInput) 
                            For i = 0 To oMatch.Count - 1 
                                 
                                strReport = strReport & oMatch(i) & " / " 
                                 
                            Next i 
                             
                        End If 
                    End If 
                End If 
                 
                 
            End Select 
            b_First = False 
            If Right(strReport, 2) = "/ " Then strReport = Left(strReport, Len(strReport) - 2) 
        Next oshp 
    Next osld 
     
     
    iFileNum = FreeFile 
    Open Environ("USERPROFILE") & "\Desktop\Abbr.txt" For Output As iFileNum 
    Print #iFileNum, strReport 
    Close iFileNum 
    Call Shell("NOTEPAD.EXE " & Environ("USERPROFILE") & "\Desktop\Abbr.txt", vbNormalFocus) 
    Set regX = Nothing 
End Sub 

Don't know how to use code?

How Does It work?

First the code loads an instance of RegEx and then loops through the text on each slide.

The Search pattern (\d+),(\d+) looks for any digit followed by , followed by any digit. The () tells RegEx to store the text found in between the brackets as $1 and $2.

The Replace line replaces the found pattern with $1 (the first number) " . " and $2 (the second number). It's use is changing continental decimals (with a comma) into UK or US decimals that use a period.

The second example uses the pattern "[A-Z?

More

You should be able to search for info on creating your own search patterns.

Here are a couple of simple ones.

.Pattern = "(19|20)\d\d"  ' year starting 19 or 20
.Pattern = "[5-9]\d%" ' a pertentage score > 50%

 

 

Back to the Index Page

POWERPOINT BLOG

Articles on your favourite sport

Free Microsoft PowerPoint Advice, help and tutorials, Template Links
This website is sponsored by Technology Trish Ltd
© Technology Trish 2007
Registered in England and Wales No.5780175
PowerPoint® is a registered trademark of the Microsoft Corporation