VBA 按空格分割字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17993389/
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
VBA split string by spaces
提问by Goatcat
I want a function in excel that i can call and pass a cell into. Input:
我想要一个 excel 函数，我可以调用它并将一个单元格传递给它。输入：
Firstname Lastname [email protected]
Firstname midname Lastname [email protected]
The number of spaces in between are random. Output should just be an array. The array can have any length since i don't know what the strings look like. Output should be:
中间的空格数是随机的。输出应该只是一个数组。数组可以有任何长度，因为我不知道字符串是什么样的。输出应该是：
Firstname, Lastname, [email protected]
Firstname, midname, Lastname, [email protected]
I will call the function from one cell like =MySplitFunction(A1), and that should put Firstname in A1, Lastname in B1, and [email protected] in C1. I created a new module and tried the following code:
我将从一个单元=MySplitFunction(A1)格调用该函数，例如，应该将名字放在 A1 中，姓氏放在 B1 中，并将 [email protected] 放在 C1 中。我创建了一个新模块并尝试了以下代码：
Function MySplitFunction(s As String) As String()
MySplitFunction = Split(s, " ")
End Function
Which gives me output
这给了我输出
Firstname
How do i get it to return the whole array? Is it even possible to write a function in one cell that will put stuff in cells close to it?
我如何让它返回整个数组？甚至有可能在一个单元格中编写一个函数，将东西放在靠近它的单元格中吗？
EDIT:
编辑：
回答by assylias
- Enter you input data in A1
- Select the B1:D1 range
- enter your formula
=MySplitFunction(A1) - make it an array formula by pressing CTRL + SHIFT + ENTER instead of just ENTER.
- 在 A1 中输入您输入的数据
- 选择 B1:D1 范围
- 输入你的公式
=MySplitFunction(A1) - 通过按 CTRL + SHIFT + ENTER 而不是仅按 ENTER 使其成为数组公式。
To remove the multiple spaces, you could amend your code like this (not super efficient but works):
要删除多个空格，您可以像这样修改代码（效率不高但有效）：
Function MySplitFunction(s As String) As String()
Dim temp As String
Do
temp = s
s = Replace(s, " ", " ") 'remove multiple white spaces
Loop Until temp = s
MySplitFunction = Split(Trim(s), " ") 'trim to remove starting/trailing space
End Function
回答by Kazimierz Jawor
Alternative solution is to:
替代解决方案是：
- use RegEx as a first step to remove all spaces
- split result of step first based on single spaces left
- moreover, because you need to return different element of the text in different cells than additional function parameter will solved that.
- 使用 RegEx 作为删除所有空格的第一步
- 根据剩余的单个空格拆分第一步的结果
- 此外，因为您需要在不同的单元格中返回不同的文本元素，而不是附加函数参数将解决这个问题。
This is proposed function:
这是建议的功能：
Public Function MySplitFunction(sMark As String, nTh As Integer) As String
On Error GoTo EH
'regexp declaration
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")
Dim tmpTXT As String
Dim tmpArr As Variant
With objRegExp
.Global = True
.Pattern = "\s+"
tmpTXT = .Replace(sMark, " ")
End With
tmpArr = Split(tmpTXT, " ")
MySplitFunction = tmpArr(nTh - 1)
Exit Function
EH:
MySplitFunction = ""
End Function
and this is screen shot presenting how it works:
这是显示其工作原理的屏幕截图：
Important!when calling function in Excel use comma to separate parameters (instead of presented semi-colon due to local-national version of excel I use).
重要的！在 Excel 中调用函数时，使用逗号分隔参数（而不是由于我使用的本地国家版本的 excel 而显示分号）。
