vba 将单元格字符串拆分为单个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13020165/
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
Split cell string into individual cells
提问by cheapkid1
I am unable to split a single cell's value into two different strings and put both of those strings in different cells.
我无法将单个单元格的值拆分为两个不同的字符串并将这两个字符串放在不同的单元格中。
For instance I want to take a measurement 10ft x 20ftvalue in a cell and take the 10ftand put it in another cell, and take the 20ftand put it in a completely different cell.
例如，我想10ft x 20ft在一个单元格中获取一个测量值10ft并将其放入另一个单元格中，20ft然后将其放入一个完全不同的单元格中。
I'd like to use a delimiter xor something, but I just don't know how to take those separations and do something with them after the split.
我想使用分隔符x或其他东西，但我只是不知道如何进行这些分隔并在拆分后对它们进行处理。
Any tips would be much appreciated. I'm still pretty new to VBA macros.
任何提示将不胜感激。我对 VBA 宏还是很陌生。
Thanks
谢谢
回答by manuerumx
The best solution is using SPLIT
最好的解决方案是使用 SPLIT
Dim strX As String
Dim sx() As String
Dim i as Integer
strX = "10FT x 20FT"
sx = Split(strX, "x")
Or maybe you can use instr function
或者你可以使用 instr 函数
Dim sVar1 as string
Dim sVar2 as string
I = InStr(1, strX, "x")
Now you know where can split int two variables
现在你知道哪里可以拆分 int 两个变量了
sVar1 = mid(strX, 1, I)
sVar2 = mid(strx,i+1)
The problem with the function is that if you have several keys in the chain with which you want to separate your function will return an array larger. For example: Dim var as string var = "x 20XP 10XP"
该函数的问题在于，如果链中有多个键，您想用这些键将函数分开，将返回一个更大的数组。例如：Dim var as string var = "x 20XP 10XP"
returns
返回
array (0) = "10"
array (1) = "p"
array (2) = "20"
array (3) = "p"
回答by brettdj
You don't actually need VBA. You can use Excel's Text to Columns
您实际上并不需要 VBA。您可以使用 Excel 的文本到列
For example, in excel-2010
例如，在excel-2010
- Data ..... Text to Columns
- Pick
delimitedand press Next - Check Spaceand put 'x' in Otherand press Next
- Finish
- 数据......文本到列
- 选择
delimited并按下一步 - 检查空间并将“x”放在其他中，然后按下一步
- 结束
回答by cheapkid1
Guess I just needed to look a little harder.
猜猜我只需要看起来更努力一点。
Sub Split_CutArea()
Dim str1() As String
Dim str2() As String
Dim avarsplit As Variant
avarsplit = Split(Cells(4, "B").Value, "x")
splitValues = Split(ActiveSheet.Cells(4, "B").Value)
ActiveSheet.Cells(22, "B").Value = splitValues(0) & splitValues(1)
ActiveSheet.Cells(23, "B").Value = splitValues(3) & splitValues(4)
End Sub
