title: Access VBA to trim space around a specific character or words(s) in a column
body: <p>I have this piece of code that originally uses the replace function(VBA) to replace certain characters in a string or column field in MS Access. The code is in a module.</p>
  
  <p>I need to modify the code so that it trims the space around certain characters. Example, if <strong><em>th</em></strong> appears alone, trim the space from the left, so that it flushes against the numbers. If it has more than one tab(space) to the right, trim it so that there is only one tab space to the right. Same for <strong><em>avenue</em></strong>, trim it so that one tab space is on the left and right.</p>
  
  <p>Here is what I have written:</p>
  
  <pre><code>Public Function TrmSpace(RemoveSpace As String) As String
  Dim UserEntry As Variant, CorrectedEntry As Variant
  Dim i As Long
  ExpectedEntry = Array("th ", " th", " th ", "TH")
  
  CorrectedEntry = Array("th", "th", "th", "th")
  TrmSpace = RemoveSpace
  For i = 0 To 3
      TrmSpace = Trim(TrmSpace, ExpectedEntry(i), CorrectedEntry(i), Compare:=vbTextCompare)
  Next
  End Function
  </code></pre>
  
  <p>I changed the function from Replace to Trim but I am doing it wrong.</p>
  
  <p>Thanks everyone!</p>
  
  <p>Guy</p>