Access VBA to trim space around a specific character or words(s) in a column

Question

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.

I need to modify the code so that it trims the space around certain characters. Example, if th 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 avenue, trim it so that one tab space is on the left and right.

Here is what I have written:

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

I changed the function from Replace to Trim but I am doing it wrong.

Thanks everyone!

Guy

Answer 1

Just to add to Jesse's answer. Instead of doing so many replaces you can simply do the following (with a reference to Excel):

Public Function TrmSpace(RemoveSpace As String) As String

  RemoveSpace = Excel.Application.WorksheetFunction.Trim(RemoveSpace)
  RemoveSpace = Replace(RemoveSpace," th","th",,,vbTextCompare)

  TrmSpace = RemoveSpace

End Function

The excel function Trim is different from the vbaTrim. The excel Trim removes all spaces that are at the beginning and end and also any double/triple/etc spaces in the string. Using vbTextCompare in your replace function will make it so it doesn't matter what mix of "th" is, so you don't need to worry if it is "TH" or "tH" or "Th", etc.

Answer 2

Trim only removes leading and trailing spaces from the beginning and end of your string. A simple solutions would be:

Public Function TrmSpace(RemoveSpace As String) As String

RemoveSpace = Replace(RemoveSpace, "    ", " ")
RemoveSpace = Replace(RemoveSpace, "   ", " ")
RemoveSpace = Replace(RemoveSpace, "  ", " ")
RemoveSpace = Replace(RemoveSpace, " th", "th")
RemoveSpace = Replace(RemoveSpace, " TH", "th")

TrmSpace = RemoveSpace
End Function

That would remove gaps up to eight spaces. If your data has actual tab characters and not spaces you'd need to replace " " with vbTab & vbTab & vbTab & vbTab.

---

A single line of code to 'squeeze' any number of repeating spaces to a one space and another to remove the leading space for th regardless of case, thus reducing the above to this:

Public Function TrmSpace(RemoveSpace As String) As String
  TrmSpace = Replace$(Replace$(Replace$(RemoveSpace, Chr$(32), Chr$(32) & Chr$(22)), _
                   Chr$(22) & Chr$(32), vbNullString), Chr$(22), vbNullString)

  TrmSpace = Replace$(TrmSpace, " th", "th", , , vbTextCompare)
End Function