使用VBA删除开头和结尾的空格，但将空格保留在字符串中 [英] Use VBA to remove leading and trailing blank spaces but keeping blanks within a string

问题描述

我正在尝试清理和格式化从会计系统获得的一组数据，并且已经能够创建VBA代码以在特定列范围内使用TRIM或CLEAN函数.

I'm trying to clean and format some set of data obtained from an accounting system and I have been able to create VBA code to use TRIM or CLEAN functions in the specific column ranges.

问题是我需要在字符串中保留空格(可以是2、3或更多空格)，但仍要删除前导/尾随空格，并且上述函数将内部空格减小为1.这是行不通的对我而言，因为数据被用作与流程其他步骤中的其他信息进行匹配的关键.请记住，前导/尾随空格可能是空格键，任何其他显示为空格甚至包含换行符的字符的结果，所以再次，我希望将所有这些空格除去但内部空格.字符串可以由字母数字字符组成.

The thing is that I need to keep the blank spaces within the strings (can be 2, 3 or more blanks) but still remove the leading/trailing spaces and the mentioned functions reduce the inner spaces to 1. This does not work for me as the data is used as a key to match other information in further steps of the process. Bare in mind that leading/trailing blanks can be the result of space bar key, any other character that appears as a blank or even contains line breaks, so again, I want all of these removed but inner blanks. Strings can be made of alphanumeric characters.

我在Private Sub中使用此代码(通过单击工作表中的按钮来执行代码).

I'm using this in a Private Sub (code is execute via a click in a button placed in the worksheet).

Dim rng1a As Range
Dim Area1a As Range
Set rng1a = Range("F2:F35001")

  For Each Area1a In rng1a.Areas
    Area1a.NumberFormat = "@"
    Area1a.Value = Evaluate("IF(ROW(" & Area1a.Address & "),CLEAN(TRIM(" & Area1a.Address & ")))")
  Next Area1a

示例(在F2:F35001范围内):

Example (in range F2:F35001):

Original:  Sample  Text   for Review.     *(there are blanks after the string)
Result:Sample Text for Review.
Desired:Sample  Text   for Review.

我进行了几周的研究，但一直没有找到一种解决方案，可以使内部空白保持原样"，并避免在论坛中重复出现尽可能多的问题.预先感谢您的帮助.

I made some research for a couple of weeks and haven't been able to find a solution that keeps the inner blanks "as is" and avoid as much as possible duplicate question in the forum. Thanks in advance for the help.

推荐答案

您可以使用正则表达式:

You can do this with regular expressions:

Option Explicit
Function trimWhiteSpace(s As String) As String
    Dim RE As Object

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .MultiLine = True
    .Pattern = "^\s*(\S.*\S)\s*"
    trimWhiteSpace = .Replace(s, "$1")
End With
End Function

正则表达式的解释

^\s*(\S.*\S)\s*

选项:区分大小写；^ $匹配行间的中断

Options: Case sensitive; ^$ match at line breaks

• 在行的开头(在字符串的开头或换行后)声明位置字符)(换行符，换行符，行分隔符，段落分隔符) ^
• 匹配单个字符，即空白字符"(ASCII空格，制表符，换行符，回车，垂直制表符，换页) \ s *
  • 在零次至无限次之间进行尽可能多的次数，并根据需要进行回馈(贪婪) *
  • 匹配不是空白字符"的单个字符(ASCII空间，制表符，行提要，回车，垂直制表符，换页) \ S
  • 匹配不是换行符(换行符)的任何单个字符.*
    • 在零次至无限次之间进行尽可能多的次数，并根据需要进行回馈(贪婪) *
    • 在零次至无限次之间进行尽可能多的次数，并根据需要进行回馈(贪婪) *

    $ 1

    • 插入通过捕获组号1而最后匹配的文本 $ 1

    使用 RegexBuddy

    另一方面，如果您要避免使用正则表达式，并且您唯一的前导/结尾空白"字符是 space ， tab 和换行，和，如果唯一的内部"空格字符是空格，则可以使用:

    On the other hand, if you want to avoid regular expressions, and if your only leading/trailing "white-space" characters are space, tab and linefeed, AND if the only "internal" white space characters are the space, you could use:

    Function trimWhiteSpace(s As String) As String
        trimWhiteSpace = Trim(Replace(Replace([a1], vbLf, ""), vbTab, ""))
    End Function
    

    请注意，VBA Trim函数(与工作表函数不同)仅删除前导和尾随空格，并使内部空间保持不变.但是，如果您需要保留字符串中的 tab ，这将无法正常工作.

    Note that the VBA Trim function (unlike the worksheet function), only removes leading and trailing spaces, and leaves internal spaces unchanged. But this won't work if you have tab's within the string that need to be preserved.

    以上任何一种都可以合并到您的宏中.

    Either of the above can be incorporated into your macro.

    这篇关于使用VBA删除开头和结尾的空格，但将空格保留在字符串中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！