Seperating music titles

[selkov]

So I would like to make spreadsheet of my music titles. But all I can do is copy the text so it all reads in one column.
What i want to do is to parse the data into separate columns. so this "3 Doors Down - The Better Life - 01 - Kryptonite.mp3" wood split at the "-" to have 4 columns.

Can anyone help me please?

-Eds

 

[steve the fish]

Put the title in A1 then this in B1 and drag across:

=TRIM(MID(SUBSTITUTE($A1,"-",REPT(" ",99)),(COLUMNS($A$1:A1)-1)*99+1,99))

 

[Lavina]

I think this is more what you are looking for:
It adds it all to an array, so you just need to loop through it and print it out. You will also need to trim off the "-" sorry bit busy

Sub init()
random = "3 Doors Down - The Better Life - 01 - Kryptonite.mp3"
Result = TextPart(random)
End Sub


Function TextPart(Str)
Dim array1(1 To 10) As Variant
Position = 1
startPosition = 1
lengthCounter = 0
counter = 0
For i = 1 To Len(Str)
testing = Mid(Str, i, 1)
counter = counter + 1
If Mid(Str, i, 1) = "-" Or Mid(Str, i, 4) = ".mp3" Then
array1(Position) = Mid(Str, startPosition, counter)
counter = 0
lengthCounter = i
startPosition = i
Position = Position + 1
End If
Next i
End Function

 

[selkov]

Omg - so close. Thanks.

But perhaps you could help me tweak it?
What I was hoping for was:
a title: "10cc - Greatest Hits 1972-1978 - 11 - Dreadlock Holiday.mp3"
to return "10cc" "Greatest Hits 1972-1978 " "11" "Dreadlock Holiday.mp3"

I did not foresee , but this"1972-1978" is an issue but more so the results now are"
"10cc" "Greatest" "1978" "11" "dreadlock holiday.mp3"

Thanks again!

 

[steve the fish]

To whom are you answering as there are two replies.

 

[mikerickson]

If this is a one time thing:
Copy the text in so you have one column of titles.
Use a helper column with the formula = SUBSTITUTE(A1," - ","~"), note the [space][dash][space]
Use TextToColumns with a tilde delimiter

Done.

 

[Lavina]

If Mid(Str, i, 1) = "-"
If there is no space between the years(1972-1978) then you can add an aditional check, if i-1 and i + 1 exists, and if they are numbers

If they do have spaces, you will need to increase the mid size to say 5 and describe situations like: number, space, dash, space, number

 

[selkov]

Steve,
I was responding to you as ii had not yet seen the others.

Lavina,
I could not get yours to work at all.

Mckericson,
What is text2columns?



Thank you all so much for such quick responses, I appreciate the assistance.

 

[Fluff]

Try this mod to Steve' formula
=TRIM(MID(SUBSTITUTE($A1," - ",REPT(" ",99)),(COLUMNS($A$1:A1)-1)*99+1,99))
Not the spaces either side of the -

 

[mikerickson]

Steve,
...
Mikerickson,
What is text2columns?
...

On the Data tab there is a built in feature called TextToColumns that will split the content of a column of cells into sub-part.