Strip RTF Tags from Text (2002 SP-2)

Topic

New Reply

Our company has a custom database that allows the user to create custom “reports” (via MS Query) that produces an Excel spreadsheet. This spreadsheet doesn’t have any special formatting, which is okay, but one particular annoyance is that fields that are RTF-formatted in the database show up as the “raw” RTF code in the corresponding Excel cell. The database support team is working on a solution from their end, but in the meantime I’d like to strip off the RTF tags and leave only the text that’s supposed to be visible (leaving it unformatted is okay). Does anyone know an easy way to do this? Is there some VBA code out there that parses out the visible text from raw RTF “code”? I’d rather not dive in and reinvent the wheel if someone out there is already “rolling.”

Thanks.

Reply | Quote

Replies

Can’t say that I have struck this before but I do have problems with old reports that get converted into excel. I’ve ended up with a selection of bits that edit cells. The likes of the following makes quick work of triming spaces off a cell.

Sub Trim_Cells()
For Each Cell In ActiveSheet.UsedRange
Cell.Value = Trim(Cell.Value)
Next
MsgBox “Trim Complete”, vbOKOnly, “Action”
End Sub
If you could paste a sample up I’d be interested to see what your problem looks like?
Cheers
Tony

Reply | Quote

Tony,

This is a little more complicated than just trimming blanks. The RTF text may look something like:

{rtf1ansideff0{fonttbl{f0fnilfcharset0 Courier New;}}
viewkind4uc1pardlang1033f0fs20 BATTERIES 15-2 AND 30-3 FAILED TESTING DURING PERFORMANCE OF
par 2S-03-00368/W. SIX ADDITIONAL BATTERIES ARE MARGINAL, ALTHOUGH
par THE PRESENT CONDITION OF THESE BATTERIES MAY SIGNIFICANTLY
par IMPROVE WITH REPLACEMENT OF THE DEFECTIVE BATTERIES.
par
par REPLACE BATTERIES 15-2 AND 30-3 AND ALLOW SYSTEM TO CHARGE AND
par STABILIZE FOR 30 DAYS. PERFORM RETEST OF ALL HVAC UPS BATTERIES
par AND EVALUATE RESULTS FOR NECESSITY OF FURTHER ACTION.
par }

The unformatted text is simply:

BATTERIES 15-2 AND 30-3 FAILED TESTING DURING PERFORMANCE OF
2S-03-00368/W. SIX ADDITIONAL BATTERIES ARE MARGINAL, ALTHOUGH
THE PRESENT CONDITION OF THESE BATTERIES MAY SIGNIFICANTLY
IMPROVE WITH REPLACEMENT OF THE DEFECTIVE BATTERIES.

REPLACE BATTERIES 15-2 AND 30-3 AND ALLOW SYSTEM TO CHARGE AND
STABILIZE FOR 30 DAYS. PERFORM RETEST OF ALL HVAC UPS BATTERIES
AND EVALUATE RESULTS FOR NECESSITY OF FURTHER ACTION.

I’m looking for an efficient way to strip off all the formatting codes that appear in the RTF text.

Reply | Quote

Tony,

This is a little more complicated than just trimming blanks. The RTF text may look something like:

{rtf1ansideff0{fonttbl{f0fnilfcharset0 Courier New;}}
viewkind4uc1pardlang1033f0fs20 BATTERIES 15-2 AND 30-3 FAILED TESTING DURING PERFORMANCE OF
par 2S-03-00368/W. SIX ADDITIONAL BATTERIES ARE MARGINAL, ALTHOUGH
par THE PRESENT CONDITION OF THESE BATTERIES MAY SIGNIFICANTLY
par IMPROVE WITH REPLACEMENT OF THE DEFECTIVE BATTERIES.
par
par REPLACE BATTERIES 15-2 AND 30-3 AND ALLOW SYSTEM TO CHARGE AND
par STABILIZE FOR 30 DAYS. PERFORM RETEST OF ALL HVAC UPS BATTERIES
par AND EVALUATE RESULTS FOR NECESSITY OF FURTHER ACTION.
par }

The unformatted text is simply:

BATTERIES 15-2 AND 30-3 FAILED TESTING DURING PERFORMANCE OF
2S-03-00368/W. SIX ADDITIONAL BATTERIES ARE MARGINAL, ALTHOUGH
THE PRESENT CONDITION OF THESE BATTERIES MAY SIGNIFICANTLY
IMPROVE WITH REPLACEMENT OF THE DEFECTIVE BATTERIES.

REPLACE BATTERIES 15-2 AND 30-3 AND ALLOW SYSTEM TO CHARGE AND
STABILIZE FOR 30 DAYS. PERFORM RETEST OF ALL HVAC UPS BATTERIES
AND EVALUATE RESULTS FOR NECESSITY OF FURTHER ACTION.

I’m looking for an efficient way to strip off all the formatting codes that appear in the RTF text.

Reply | Quote

Can’t say that I have struck this before but I do have problems with old reports that get converted into excel. I’ve ended up with a selection of bits that edit cells. The likes of the following makes quick work of triming spaces off a cell.

Sub Trim_Cells()
For Each Cell In ActiveSheet.UsedRange
Cell.Value = Trim(Cell.Value)
Next
MsgBox “Trim Complete”, vbOKOnly, “Action”
End Sub
If you could paste a sample up I’d be interested to see what your problem looks like?
Cheers
Tony

Reply | Quote

Since Word reads RTF, can you open in Word and Copy and Paste to Excel?

Reply | Quote

John,

Yes, that would work — in fact, that’s how I extracted the unformatted text to include in my response to Tony’s post. The problem is that the spreadsheet can have hundreds of cells that need this reformatting. Doing it a cell at a time (copy cell contents, paste in Notepad, save as .rtf, open in Word, copy, paste back into Excel) would be a little tedious. I suppose I could do this (or something similar) through VBA automation, but it seems like there ought to be a more efficient way.

Reply | Quote

Check out a cunction at this site. I found it via googling on [VBA convert rtf]. You can probably use other variants to search.

Steve

Reply | Quote

Ooooh, thanks Steve! I did Google around a bunch prior to posting the question here, but did not come across the link you found (I used various combinations of VBA, strip, code, RTF, rich text, etc but not the exact one you were successful with). Looks like it ought to work. I’ll give it a shot.

Thanks again.

Reply | Quote

Ooooh, thanks Steve! I did Google around a bunch prior to posting the question here, but did not come across the link you found (I used various combinations of VBA, strip, code, RTF, rich text, etc but not the exact one you were successful with). Looks like it ought to work. I’ll give it a shot.

Thanks again.

Reply | Quote

Check out a cunction at this site. I found it via googling on [VBA convert rtf]. You can probably use other variants to search.

Steve

Reply | Quote

John,

Yes, that would work — in fact, that’s how I extracted the unformatted text to include in my response to Tony’s post. The problem is that the spreadsheet can have hundreds of cells that need this reformatting. Doing it a cell at a time (copy cell contents, paste in Notepad, save as .rtf, open in Word, copy, paste back into Excel) would be a little tedious. I suppose I could do this (or something similar) through VBA automation, but it seems like there ought to be a more efficient way.

Reply | Quote

Since Word reads RTF, can you open in Word and Copy and Paste to Excel?

Reply | Quote