How to split a string of text!

peteroldfield1971

New Member
Joined
Apr 27, 2005
Messages
48
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.
 

Sharkie21

Active Member
Joined
Nov 2, 2005
Messages
319
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

Active Member
Joined
Nov 2, 2005
Messages
319
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

Active Member
Joined
Nov 5, 2004
Messages
336
Sharkie21 beat me to the post. I had a similar idea;

Code:
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
 

Forum statistics

Threads
1,078,401
Messages
5,339,996
Members
399,346
Latest member
t0land

Some videos you may like

This Week's Hot Topics

Top