等效于 VBA TRIM 函数 Excel 与工作表函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10976807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Equivalent of VBA TRIM function Excel with worksheet function
提问by stexcec
I know that the TRIM function as worksheet function and as VBA function returns different results:
我知道 TRIM 函数作为工作表函数和作为 VBA 函数返回不同的结果：
- the worksheet function removes all spaces from a text string except for single spaces between words
- while the VBA function only removes leading and trailing spaces.
- 工作表函数从文本字符串中删除除单词之间的单个空格之外的所有空格
- 而 VBA 函数只删除前导和尾随空格。
There is a way to emulate the VBA behavior with worksheet functions, WITHOUT a custom VBA function?
有没有一种方法可以使用工作表函数模拟 VBA 行为，而无需自定义 VBA 函数？
回答by lori_m
Using only worksheet functions you could try this for a text string in A1:
仅使用工作表函数，您可以尝试对 A1 中的文本字符串执行此操作：
=REPLACE(LEFT(A1,MATCH(2,1/(MID(A1,MMULT(ROW(INDIRECT("1:"&LEN(A1))),1),1)<>" "))),1,FIND(LEFT(TRIM(A1)),A1)-1,"")
Finding the position of the last character that's not a space is not straightforward due to the lack of string reverse worksheet functions.
由于缺少字符串反向工作表函数，查找不是空格的最后一个字符的位置并不简单。
If you don't want to use VBA it's much simpler to use Data|Text to Columnswith the options:
如果您不想使用 VBA，那么使用Data|Text to Columns和以下选项要简单得多：
- Fixed Width
- No break lines
- Destination: B1
- 固定宽度
- 没有断线
- 目的地：B1
which also removes leading and trailing spaces.
这也删除了前导和尾随空格。
回答by Siddharth Rout
the worksheet function removes all spaces from a text string except for single spaces between words
工作表函数从文本字符串中删除除单词之间的单个空格之外的所有空格
No that is not completely true :) Worksheet TRIMfunction does not remove the nonbreaking space character ( ). To remove the nonbreaking space character, you have a different function called CLEAN()
不，这不完全正确:) 工作表TRIM函数不会删除不间断空格字符 ( )。要删除不间断空格字符，您可以使用一个不同的函数CLEAN()
If you want to use the Worksheet Function Trimin VBA then you can use it like this
如果你想Trim在 VBA 中使用工作表函数，那么你可以像这样使用它
Application.WorksheetFunction.Trim(Range("A1").Value)
In Excel Worksheet you can use =TRIM()
在 Excel 工作表中，您可以使用 =TRIM()
回答by PeteGO
You can write a function in VBA that you can refer to in your worksheet.
您可以在 VBA 中编写一个可以在工作表中引用的函数。
For example:
例如：
Public Function MyTrim(text As String) As String
MyTrim = Trim(text)
End Function
Then in your worksheet, assuming the text you want to trim is in cell A1:
然后在您的工作表中，假设您要修剪的文本在单元格 A1 中：
=MyTrim(A1)
