VBA用空格拆分字符串 [英] VBA split string by spaces

问题描述

我想要一个excel中的函数，我可以调用并传递一个单元格。输入：

 名字姓氏[e-mail] 
名字中名姓氏[e-mail] 
  

之间的空格数是随机的。输出应该是一个数组。数组可以有任何长度，因为我不知道字符串的外观。输出应该是：

 名字，姓氏，[e-mail] 
名字，中名，姓氏，电子邮件@邮件我将从一个单元格中调用函数，如 = MySplitFunction（A1） ），应该将C1中的名字放在A1，姓氏B1和[e-mail]中。我创建了一个新的模块并尝试了以下代码：
 函数MySplitFunction（s As String）As String（）
 MySplitFunction = Split（s，）
结束函数
  
 p> 
 
 
 名字
  
如何让它返回整个数组？甚至可以在一个单元格中写入一个函数，将其放在靠近单元格的单元格中。
 
 
 
编辑：
 
 
 
  
解决方案
 
 
 
在A1输入您的输入数据
 
 
选择B1：D1范围
 
 
 enter您的公式 = MySplitFunction（A1） 
 
 
通过按CTRL + SHIFT + ENTER而不是按ENTER键使其成为数组公式。 li> 
 
 
 
 
要删除多个空格，您可以修改这样的代码（效率不高但不起作用）：
 函数MySplitFunction（s as String）As String（）
 Dim temp As String 
 
 Do 
 temp = s 
s =替换（s，，）'删除多个空格
循环直到temp = s 
 
 MySplitFunction = Split（Trim（s），） '修剪以删除起始/尾随空格
结束函数
  
 
I want a function in excel that i can call and pass a cell into. Input:
Firstname          Lastname      [e-mail]       
Firstname      midname     Lastname      [e-mail]
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, [e-mail]       
Firstname, midname, Lastname, [e-mail]
I will call the function from one cell like =MySplitFunction(A1), and that should put Firstname in A1, Lastname in B1, and [e-mail] in C1. I created a new module and tried the following code:
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:

解决方案

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.


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


                        
这篇关于VBA用空格拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！