需要 Excel VBA 宏根据特定字符将单元格划分为多个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6625494/
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
Need an Excel VBA macro to divide a cell into multiple cells depending on a specific character
提问by Murray
I have a mailing list full of names and addresses for university prospectuses, taken from a .tab file, in an excel document. But the addresses are contained within a single cell, with each line seperated by a carriage return.
我有一个邮件列表，其中包含大学招股说明书的姓名和地址，这些列表取自 .tab 文件，位于 excel 文档中。但是地址包含在单个单元格中，每行由回车分隔。
I really need a macro I can run which will seperate the different parts into different cells. I basically know the structure, but have no idea about VBA's capabilities.
我真的需要一个可以运行的宏，它将不同的部分分成不同的单元格。我基本上知道结构，但不知道 VBA 的功能。
It needs to scan until a carriage return appears, put the preceding data, minus the carriage return into a new cell, then continue on until there are no characters left in the cell, as there is no carriage return at the end.
它需要扫描直到出现回车，将前面的数据减去回车放入一个新的单元格，然后继续直到单元格中没有剩余字符，因为最后没有回车。
Is this possible?
这可能吗？
回答by Jon Egerton
You can do this without VBA as follows:
您可以在没有 VBA 的情况下执行此操作，如下所示：
(I'm assuming your data is in column A)
（我假设您的数据在 A 列中）
- Use this formula in Column B to replace the carriage returns:
=SUBSTITUTE(A1,CHAR("10"),"|") - Copy Column B and use PasteSpecial - Values to copy the actual Data into Column C
- Use Text-To-Columns on Column C to split the text by | into separate cells from D onwards
- 在 B 列中使用此公式替换回车符：
=SUBSTITUTE(A1,CHAR("10"),"|") - 复制 B 列并使用 PasteSpecial - Values 将实际数据复制到 C 列
- 使用 C 列上的 Text-To-Columns 按 | 拆分文本 从 D 开始分成单独的单元格
Hope this helps
希望这可以帮助
回答by Jean-Fran?ois Corbett
Here's code for your VBA macro:
这是您的 VBA 宏的代码：
Dim rngSource As Range
Dim rngDestination As Range
Set rngSource = Sheet1.Range("A1:A5") ' or wherever your list is
Set rngDestination = Sheet1.Range("C1") ' parsed data will be placed here
rngSource.TextToColumns _
Destination:=rngDestination , _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:=vbLf ' This is where you define your delimiter character
where vbLfis the same as Chr(10), assuming that's your delimiter. Depending on your flavour of carriage return, you may need to use vbCr(or, equivalently, Chr(13)) instead.
wherevbLf与 相同Chr(10)，假设这是您的分隔符。根据您的回车风格，您可能需要使用vbCr（或等效地，Chr(13)）来代替。
Note that @Jon Egerton's non-macro way of doing it works just fine. However, if it needs to be done more than once, I tend to grow weary of all the clicking, selecting, typing, copying, special pasting, pasting in the wrong place and having to start over, etc. I find VBA macros are much more reusable. It's a matter of taste, I guess.
请注意，@Jon Egerton 的非宏观方式工作得很好。但是，如果需要多次完成，我往往会厌倦所有的点击、选择、打字、复制、特殊粘贴、粘贴到错误的位置以及不得不重新开始等。我发现 VBA 宏很多更可重复使用。我想这是一个品味问题。
