Need an Excel VBA macro to divide a cell into multiple cells depending on a specific character

2
votes

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.

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.

Is this possible?

2
excelvba

2 Answers

4
votes

You can do this without VBA as follows:

(I'm assuming your data is in column A)

  1. Use this formula in Column B to replace the carriage returns: =SUBSTITUTE(A1,CHAR("10"),"|")
  2. Copy Column B and use PasteSpecial - Values to copy the actual Data into Column C
  3. Use Text-To-Columns on Column C to split the text by | into separate cells from D onwards

Hope this helps

4
votes

Here's code for your VBA macro:

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 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.

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.