需要一个Excel VBA宏根据特定字符将单元格划分为多个单元格 [英] Need an Excel VBA macro to divide a cell into multiple cells depending on a specific character

问题描述

我的邮件列表中填写了大学招股说明书的名称和地址，以.tab文件的形式，以excel文件形式提供。但是地址包含在单个单元格中，每行都由回车符分隔。

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.

我真的需要一个可以运行的宏，将将不同的部分分隔成不同的部分细胞。我基本上知道结构，但不知道VBA的功能。

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.

它需要扫描直到回车出现，将前面的数据，减去回车符新单元格，然后继续，直到单元格中没有字符，因为最后没有回车。

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.

这是可能吗？

推荐答案

这是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

其中 vbLf 与 Chr（10）相同， ng这是你的分隔符。根据您的回车风味，您可能需要使用 vbCr （或等价于 Chr（13））代替。

where vbLf is 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.

请注意，@Jon Egerton的非宏观方式可以正常工作。但是，如果需要不止一次地完成，我就会厌倦所有的点击，选择，打字，复制，特殊粘贴，粘贴在错误的地方，重新开始等等。我发现VBA宏很多更可重用。这是一个味道的问题，我猜。

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.

这篇关于需要一个Excel VBA宏根据特定字符将单元格划分为多个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！