VBA按空格分割字符串

Question

我希望在Excel中有一个可以调用并传递单元格的函数。输入：

Firstname          Lastname      [e-mail]       
Firstname      midname     Lastname      [e-mail]

空格数量是随机的。输出应该是一个数组。数组长度可以任意，因为我不知道字符串的样子。输出应该是：

Firstname, Lastname, [e-mail]       
Firstname, midname, Lastname, [e-mail]

我将从一个单元格中调用该函数，如 =MySplitFunction(A1)，这应该会把名字放在A1单元格，姓氏放在B1单元格，电子邮件地址[e-mail]放在C1单元格。我创建了一个新模块并尝试了以下代码：

Function MySplitFunction(s As String) As String()
    MySplitFunction = Split(s, " ")
End Function

这给我输出结果

Firstname

如何使其返回整个数组？在一个单元格中编写能够将内容放入相邻单元格的函数，是否可能？

编辑：

- Goatcat

Answer 1

• 在A1中输入您的输入数据
• 选择B1:D1范围
• 输入您的公式=MySplitFunction(A1)
• 通过按下CTRL + SHIFT + ENTER而不是仅按下ENTER将其设置为数组公式。

要删除多个空格，您可以像这样修改代码（不是特别高效但起作用）：

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

- assylias

---

这个有点用，但是我无法去掉所有额外的空格。有什么想法吗？ - Goatcat

我想做的是将所有多个空格替换为一个单独的空格，然后在其上运行Split(s，" ")。你有什么想法？ - Goatcat

我已经让它正常工作了，但我不得不在当前替换行下方添加´s = Replace(s, Chr(160), " ")´，因为它包含所谓的“不间断空格”。我编辑了回答以包括这一点。非常感谢您的帮助！ - Goatcat

Answer 2

另一个解决方案是：

1. 首先使用正则表达式来删除所有空格
2. 基于剩下的单个空格对第一步的结果进行分割
3. 此外，由于您需要在不同的单元格中返回文本的不同部分，因此可以使用附加函数参数来解决该问题。

以下是建议的函数：

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

以下是它的工作原理的屏幕截图：

重要提示！ 在调用Excel函数时，请使用逗号分隔参数（而不是显示的分号，因为我使用的是本地版本的Excel）。

- Kazimierz Jawor

---

我按照您在B2中输入的公式精确地输入，但是出现了错误“无法更改数组的一部分”。 - Goatcat

我在上一个回答的最后一条评论中提到的逗号和分号有什么区别？ - Kazimierz Jawor

检查原始帖子的编辑。我认为我不应该使用逗号。我通常总是使用分号。 - Goatcat

你说得对，分号对你没问题。1. 暂时注释掉“On error...”这行代码，并告诉我哪一行出错了。2. 回答你的附加问题（来自编辑）- 你可以将这个函数改进为数组函数，并在Excel中使用Ctrl+Shift+Enter调用它。 - Kazimierz Jawor

正则表达式很不错的想法，加上一个 CHAR(160) 组件到模式中也是个好主意。 - brettdj