Powerpoint VBA Remove carriage return from formatted text box

Question

I am trying to remove a carriage return from a text box with formatted text:

For i = ThisShape.TextFrame2.TextRange.Runs.Count To 1 Step -1
  ThisText = ThisShape.TextFrame2.TextRange.Runs(i).Text
  ThisText = Replace(ThisText, Chr(13), "")
  ThisShape.TextFrame2.TextRange.Runs(i).Text = ThisText
Next i

For some odd reason, the text box remains with a carriage return. Any help would be appreciated.

David

Answer 1

The CLEAN function is rarely used, thus often forgotten.

CLEAN removes non printable characters (ASCII 0 through 31) from the given text. You can use it in VBA:

myStr = Application.WorksheetFunction.Clean(myStr)

...or as an Excel worksheet function:

=CLEAN(A1)

---

Example:

Sub cleanDemo()

    Dim mystr As String

    'populate string with linefeeds and carriage returns.
    'note: vbLf = chr(10): vbCr = chr(13) : vbCrLf = vbCr+vbLf
    mystr = "A" & vbLf & "B" & vbCr & "C" & vbCrLf & "D"

    'display string and it's length
    MsgBox mystr, , "myStr is " & Len(mystr) & " char long."

    'remove ASCII 0 through 31
    mystr = Application.WorksheetFunction.Clean(mystr)

    'display string and it's length
    MsgBox mystr, , "myStr is " & Len(mystr) & " char long."

End Sub

...returns:

---

List all characters within string:

The procedure lists the characters and their codes found in the specified string:

Sub showAllChars(strIn as String)
'Lists all ASCII character codes used in the specified string.
'Adaptable to UNICODE by changing Asc to AscW and Chr to ChrW
Dim strIn As String, c As String, p As Long

For p = 1 To Len(strIn)
    c = Mid(strIn, p, 1)
    Debug.Print "Pos#" & p & ": Chr(" & Asc(c) & ") =[";
    Select Case Asc(c)
        Case 10
            Debug.Print "LF]", ;
        Case 13
            Debug.Print "CR]", ;
        Case Else
            Debug.Print c & "]", ;
    End Select
    If p / 5 = p \ 5 Then Debug.Print
Next p
Debug.Print "Done. (Length=" & Len(strIn) & ")"
End Sub

Example Output:

Pos#1: Chr(65) =[A]         Pos#2: Chr(66) =[B]         Pos#3: Chr(13) =[CR]        
Pos#4: Chr(67) =[C]         Pos#5: Chr(63) =[?]         Pos#6: Chr(68) =[D]         
Pos#7: Chr(17) =[]          Pos#8: Chr(69) =[E]         Pos#9: Chr(70) =[F]         
Pos#10: Chr(63) =[?]        Pos#11: Chr(71) =[G]        Pos#12: Chr(10) =[LF]       
Pos#13: Chr(72) =[H]        Pos#14: Chr(73) =[I]        Done. (Length=14)

---

More Information:

• Office.com : CLEAN Function (Excel/VBA)

• MSDN : WorksheetFunction.Clean Method (Interop)

• ascii.cl : ASCII Codes Tables

• MSDN : Chr & ChrW Functions (Interop)

• MSDN : Asc & AscW Functions (Interop)

• Wikpedia: ASCII

• Wikpedia : Control (aka Non-Printable) Characters