How to split a string of text!

[peteroldfield1971]

Hi,

Can any one help me do the following?

In column A I have a string of text eg:

1. in my life - the beatles

In cell B1 i want "1", in cell C1 "2in my life" and in D1 "the beatles"

I would like to run this with a button if possible to repeat the macro through rows 1 to 180.

Any one help?

Many thanks.

 

[peteroldfield1971]

Correction!

That should read C1 "in my life".

 

[Sharkie21]

Are there special markers indicating where to stop grabing the string?
ie: number followed by a "." Then Title followed by "-". Remaining is the band name?

 

[Sharkie21]

I'll assume that those are the delimited points. If so something like this would work

Public Sub whatever()
Dim intPeriodPos As Integer
Dim intHyphenPos As Integer
Dim currRow As Long
Dim currString As String

currRow = 1
For I = 0 To 180 'how ever many

currString = Range("A" & currRow)
intPeriodPos = InStr(currString, ".")
intHyphenPos = InStr(currString, "-")

Range("B" & currRow).Value = Left(currString, intPeriodPos)
Range("C" & currRow).Value = Mid(currString, intPeriodPos + 2, intHyphenPos - intPeriodPos - 2)
Range("D" & currRow).Value = Right(currString, Len(currString) - intHyphenPos - 1)
Next

End Sub

 

[Fat Cat]

Sharkie21 beat me to the post. I had a similar idea;

Private Sub CommandButton1_Click()
Dim myText1 As Variant
Dim myText2 As Variant

Dim delimiter1 As String
Dim delimiter2 As String

delimiter1 = ". "
delimiter2 = " - "

For i = 1 To 16
    myText1 = Split(Cells(i, 1).Text, delimiter1)
    Cells(i, 1) = myText1(0)
    myText2 = Split(myText1(1), delimiter2)
    Cells(i, 2) = myText2(0)
    Cells(i, 3) = myText2(1)
Next i
End Sub

 

[peteroldfield1971]

Thanks guys!