Rich text format with formatting tags in Excel to unformatted text

0 votes

I have roughly 12000 RTF-filled cells in Excel (including formatting tags). To access the unformatted content, I must parse them.

This is the example of one of the cells with text:

{\fonttbl{\f0 \froman \fcharset0 Times New Roman;}{\f1 \fswiss \fcharset238
{\colortbl ;\red255\green255\blue255 ;\red0\green0\blue0 ;}
{\stylesheet{\fs24\cf2\cb1 Normal;}{\cs1\cf2\cb1 Default Paragraph Font;}}
\plain\plain\f1\fs24\pard TPR 0160 000\par IPR 0160 000\par OB-R-02-28\par}

And all I really need is this:

TPR 0160 000
IPR 0160 000

The issue with simply iterating over the cells to eliminate any extraneous formatting is that not all of the 12000 cells are as easy as this. As a result, I would have to manually review numerous versions and write numerous variants, and in the end, there would still be a tonne of manual labor to be done.

However, if I copy the content of a single cell to an empty text document, save it as RTF, and then open it in MS Word, it immediately parses the text and gives me the results I need. Unfortunately, doing so for 12000 cells is rather cumbersome.

Therefore, I was considering using a VBA macro to copy the output back to the original cell after copying the contents of the cell to Word and forcing parsing. Sadly, I'm not really sure how to go about doing that.

Does anyone know the answer? or another strategy? If there is a solution or a push in the right direction, I would be really appreciative.

Oct 20, 2022 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

This function ought to be helpful if you decided to use Word to parse the text. You'll need a reference to the MS Word Object Library, as the comments suggest.

Function ParseRTF(strRTF As String) As String
Dim wdDoc As Word.Document 'Ref: Microsoft Word 11.0 Object Library'
Dim f     As Integer       'Variable to store the file I/O number'

'File path for a temporary .rtf file'
Const strFileTemp = "C:\TempFile_ParseRTF.rtf"

'Obtain the next valid file I/O number'
f = FreeFile

'Open the temp file and save the RTF string in it'
Open strFileTemp For Output As #f
    Print #f, strRTF
Close #f

'Open the .rtf file as a Word.Document'
Set wdDoc = GetObject(strFileTemp)

'Read the now parsed text from the Word.Document'
ParseRTF = wdDoc.Range.Text

'Delete the temporary .rtf file'
Kill strFileTemp

'Close the Word connection'
wdDoc.Close False
Set wdDoc = Nothing
End Function

You could call it for each of your 12,000 cells using something similar to this:

Sub ParseAllRange()
Dim rngCell As Range
Dim strRTF  As String

For Each rngCell In Range("A1:A12000")

    'Parse the cell contents'
    strRTF = ParseRTF(CStr(rngCell))

    'Output to the cell one column over'
    rngCell.Offset(0, 1) = strRTF
End Sub

The ParseRTF function takes about a second to run (on my machine at least), so for 12,000 cells, this will work out at about three and a half hours.

answered Oct 20, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Change date format of cell in excel from to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
1 answer

Excel VBA userform paste text in expanded format (not just to one cell)

It should work if you simply supply ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP