Convert RTF (Rich Text Format) code to plain text in Excel

Question

I export the database query to Excel and I get the rows with RTF formatting.

How to convert these fields to plain text? I found answers that are pretty old, so I was wondering if anyone knew a way.

Answer 1

Another alternative might be to use Microsoft Rich Textbox Control (but cannot test it in x64 Office)

Sub rtfToText()
    With CreateObject("RICHTEXT.RichtextCtrl") ' or add reference to Microsoft Rich Textbox Control for early binding and With New RichTextLib.RichTextBox
        .SelStart = 0                          ' needs to be selected
        .TextRTF = Join(Application.Transpose(Cells.CurrentRegion.Columns(1)))
        [C1] = .Text                           ' set the destination cell here

        ' or if you want them in separate cells:
        a = Split(.Text, vbNewLine)
        Range("C3").Resize(UBound(a) + 1) = Application.Transpose(a)
    End With
End Sub

Answer 2

The .Net Framework class RichTextBox can perform the conversion. Fortunately, this class has a ComVisibleAttribute set, so it can be used from VBA without too much difficulty.

.tlb .

% SYSTEMROOT%\Microsoft.NET\Framework\currentver\

regasm /codebase system.windows.forms.dll

system.windows.forms.tlb. .tlb , , .Net System.Windows.Forms RichTextBox VBA.

.tlb VBA Tools- > References VBA IDE.

Access, .

Dim rtfSample As String
rtfSample = "{\rtf1\ansi\deflang1033\ftnbj\uc1 {\fonttbl{\f0 \froman \fcharset0 Times New Roman;}{\f1 \fswiss \fcharset0 Segoe UI;}} {\colortbl ;\red255\green255\blue255 ;} {\stylesheet{\fs22\cf0\cb1 Normal;}{\cs1\cf0\cb1 Default Paragraph Font;}} \paperw12240\paperh15840\margl1440\margr1440\margt1440\margb1440\headery720\footery720\deftab720\formshade\aendnotes\aftnnrlc\pgbrdrhead\pgbrdrfoot \sectd\pgwsxn12240\pghsxn15840\marglsxn1440\margrsxn1440\margtsxn1440\margbsxn1440\headery720\footery720\sbkpage\pgnstarts1\pgncont\pgndec \plain\plain\f1\fs22\lang1033\f1 hello question stem\plain\f1\fs22\par}"

Dim miracle As System_Windows_Forms.RichTextBox
Set miracle = New System_Windows_Forms.RichTextBox
With miracle
    .RTF = rtfSample 
    RTFExtractPlainText = .TEXT
End With

MsgBox RTFExtractPlainText(rtfSample)

hello question stem

, .tlb \Framework64\ 64- Windows 64- Office. 64- Win10 32- Office 2013, 32- .tlb .