访问VBA以在列中的特定字符或单词周围修剪空间 [英] Access VBA to trim space around a specific character or words(s) in a column

问题描述

我有这段代码，最初使用替换功能(VBA)替换MS Access中字符串或列字段中的某些字符.该代码在模块中.

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.

这是我写的:

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.

谢谢大家！

人

推荐答案

Trim仅删除字符串开头和结尾的前导和尾随空格.一个简单的解决方案是:

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

这将删除最多八个空格的间隙.如果您的数据中包含实际的制表符而不是空格，则需要用vbTab&替换" vbTab& vbTab& vbTab.

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.

单行代码可以将任意数量的重复空间压缩"到一个空间，而无论大小写如何，都可以删除th的前导空间，从而将上述内容减少为:

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

这篇关于访问VBA以在列中的特定字符或单词周围修剪空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！