vba 将字符串拆分为多个字符的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21652468/
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
Function to split a string into multiple characters
提问by user3288319
I want to create a custom function that takes the selected parameter and splits its content on different cells.
我想创建一个自定义函数，该函数采用所选参数并将其内容拆分到不同的单元格中。
example :
例子 ：
A1=ABCDE
A1=ABCDE
becomes
变成
B1=A, C1=B, D1=C, E1=D, F1=E
B1=A, C1=B, D1=C, E1=D, F1=E
so this is what I tried :
所以这就是我尝试过的：
Function SplitWord(Word)
NbCar = Len(Word) // get the number of cardinals of the text
SplitWord = Left(Word, 1) // put the first letter in the cell that called the function
t = NbCar - 1
For i = 1 To t
ActiveCell.Offset(0, i) = Right(Left(Word, i), 1)
Next
End Function
回答by rbhattad
- Enter the contents to split in cell A1.
- Paste this in cell B1: =MID($A$1,COLUMN()-COLUMN($B$1)+1,1)
- Drag to the right
- 在单元格 A1 中输入要拆分的内容。
- 将其粘贴到单元格 B1 中：=MID($A$1,COLUMN()-COLUMN($B$1)+1,1)
- 向右拖动
回答by Peter Albert
You could also do this with simple Excel formulas - place this cell in B1 and copy it to C1-F1:
您也可以使用简单的 Excel 公式执行此操作 - 将此单元格放在 B1 中并将其复制到 C1-F1：
=MID(A1,COLUMN()-COLUMN($B)+1,1))
回答by EMAD
Try =MID($A1,COLUMNS($A$1:A$1),1), then drag it left it shall come.
尝试=MID($A1,COLUMNS($A$1:A$1),1)，然后将其向左拖动它会来。
回答by Dmitry Pavliv
You can't modify cells in UDF, when calling this UDF from sheet (actually there is possible ways, but they're sophisticated and I don't recomend to use them). You can use this funciton instead:
从工作表调用此 UDF 时，您无法修改 UDF 中的单元格（实际上有可能的方法，但它们很复杂，我不建议使用它们）。您可以改用此功能：
Function SplitWord(Word As String) As String()
Dim res() As String
ReDim res(1 To Len(Word))
For i = 1 To Len(Word)
res(i) = Mid(Word, i, 1)
Next
SplitWord = res
End Function
How to use it:
如何使用它：
- Select destination range (e.g.
B1:F1) - With selected rangeenter formula in formula bar
=SplitWord(A1) - Press CTRL+SHIFT+ENTERto evaluate it
- 选择目的地范围（例如
B1:F1） - 使用选定的范围在公式栏中输入公式
=SplitWord(A1) - 按CTRL+ SHIFT+ENTER对其进行评估
If your destination range in one column (e.g. B1:B5) use =TRANSPOSE(SplitWord(A1))
如果您的目标范围在一列（例如B1:B5）中，请使用=TRANSPOSE(SplitWord(A1))
回答by katie lu
Place this in any first row cell: =MID($A$1,ROW(),1)
将其放在任何第一行单元格中：=MID($A$1,ROW(),1)
回答by Gabriele
1) Place text you want to split in cell A1 (image)
1) 将要拆分的文本放入单元格 A1 ( image)
2) Then paste this function into any cell you want (image)
2）然后将此功能粘贴到您想要的任何单元格中（图像）
=MID($A1;COLUMN(A1)-COLUMN($A1)+1;1)
3) Move the mouse cursor over the little block in the lower right corner of the cell in which you pasted the above-mentioned function (image)
3) 将鼠标光标移到粘贴上述函数的单元格右下角的小方块上（图片）
4) Click and hold on the fill handle, and drag to the right to fill in the series (image)
4) 单击并按住填充手柄，向右拖动以填充系列（图像）
