title: Removing tags from formatted text in Excel cells
body: <p>Walk with me for a moment.</p>
  
  <p>I have built an Access application to manage data for an internal project at my company. One of the functions of this application is queries the database, then outputs the queries to an Excel spreadsheet, then formats the spreadsheet to spec.</p>
  
  <p>One of the cells of the output is a large amount of text from a Rich Text Memo field in the database. When the rich text is sent to Excel it carries with it HTML tags indicating <strong>bold</strong> or <em>italic</em>, so for the output I have to add the formatting and remove the tags.</p>
  
  <p>Here is an example of the text I need to format (this text is in a single cell):</p>
  
  <pre><code>For each participant, record 1 effort per lesson delivered
  
     • Time Spent = # minutes spent on lesson
  
  &lt;strong&gt;OR&lt;/strong&gt;
  For each participant, record 1 effort per month
  
     • Time Spent = total # minutes spent on lessons that month
  
      &lt;strong&gt;Note:&lt;/strong&gt; Recording 1 effort per lesson is recommended but not     required
  &lt;strong&gt;Note:&lt;/strong&gt; Use groups function in ABC when appropriate (see &lt;u&gt;Working With     Groups&lt;/u&gt; in ABC document library on the ABC portal)
  </code></pre>
  
  <p>I have a three neat little recursive functions for formatting the text, here is the bolding function:</p>
  
  <pre><code>Function BoldCharacters(rng As Range, Optional ByVal chrStart As Long)
  'This will find all the "&lt;strong&gt;&lt;/strong&gt;" tags and bold the text in between.
  Dim tagL As Integer
  tagL = 8
  rng.Select
  
  If chrStart = 0 Then chrStart = 1
  
  b1 = InStr(chrStart, ActiveCell.Value, "&lt;strong&gt;") + tagL
      If b1 = tagL Then Exit Function
  b2 = InStr(b1, ActiveCell.Value, "&lt;/strong&gt;")
  
  ActiveCell.Characters(Start:=b1, Length:=b2 - b1).Font.Bold = True
  
  'Remove the tags
  'ActiveCell.Characters(Start:=1, Length:=1).Delete
  'ActiveCell.Characters(Start:=b2 - tagL, Length:=tagL + 1).Delete
  
  'Recursion to get all the bolding done in the cell
  Call BoldCharacters(ActiveCell, b2 + tagL + 1)
  
  End Function
  </code></pre>
  
  <p>Now here's the issue. This formats the text nicely. But the "ActiveCell.Characters.Delete" method <em>fails</em> when I attempt to use it to remove the tags because the cell contains more than 255 characters. So I can't use the delete method.</p>
  
  <p>And when I do this:</p>
  
  <pre><code>With xlApp.Selection
      .Replace what:="&lt;strong&gt;", replacement:=""
  </code></pre>
  
  <p>The tags are all removed, but the formatting is all destroyed! So what's the point!?</p>
  
  <p><strong>I'm looking for a way of formatting my text and removing the tags.</strong> I'm considering taking the large bit of text and 'chunking' it up into a number of cells, processing the formatting and re-assembling, but that sounds difficult, prone to error, and might not even work.</p>
  
  <p>Any ideas!? </p>
  
  <p>Thanks!</p>