Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strip RTF Tags from Text (2002 SP-2)

    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.

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip RTF Tags from Text (2002 SP-2)

    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

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Strip RTF Tags from Text (2002 SP-2)

    Since Word reads RTF, can you open in Word and Copy and Paste to Excel?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip RTF Tags from Text (2002 SP-2)

    Tony,

    This is a little more complicated than just trimming blanks. The RTF text may look something like:
    <font face="Georgia">
    {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 }
    </font face=georgia>
    The unformatted text is simply:
    <font face="Georgia">
    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.
    </font face=georgia>
    I'm looking for an efficient way to strip off all the formatting codes that appear in the RTF text.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip RTF Tags from Text (2002 SP-2)

    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Strip RTF Tags from Text (2002 SP-2)

    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

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip RTF Tags from Text (2002 SP-2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •