Change Music notes into columns 1

[ftpdoo]

Hi - I have a list of music notes and I want to covert them from the following:

AAAABBCDGGAABCD'A

To the following columns:

A
A
A
A
B
B
C
D
G
G
A
A
B
C
D'
A

NOTE the tricky D'!! Does anybody have any ideas how to convert these music notes into columns in excel?

Many thanks,
Jonny

 

[GlennUK]

Assuming that the list is in a single cell, and that you mean that you want the results in separate rows, then this should do it:

Option Compare Text
Option Explicit
Sub splitnotes()
    Dim strBuffer As String
    Dim i As Integer
    Dim intOutputPos As Integer
    Dim cOutCell As Range
    Dim c As String
    strBuffer = ""
    intOutputPos = 0
    Set cOutCell = Range("A3")
    For i = 1 To Range("A1").Characters.Count
        c = Mid(Range("A1"), i, 1)
        If InStr(1, "abcedfg", c) <> 0 Then
            cOutCell.Offset(intOutputPos) = strBuffer
            strBuffer = c
            intOutputPos = intOutputPos + 1
        Else
            strBuffer = strBuffer & c
        End If
    Next
    cOutCell.Offset(intOutputPos) = strBuffer

End Sub

This looks for the original list in cell A1, and puts the results into the range from cell A4 downwards ( that's cell A3 offset by 1, then 2, then 3 etc etc ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[ftpdoo]

Thanks Glenn this works PERFECTLY - I'll be in fine tune now!

Thanks again,
Jonny