Text to columns excel formula

[Celticshadow]

Hi Forum

May I ask members if they would be kind enough to supply a formula to split the below info into separate columns by way of formula. An explanation of the info is as follows
2-10 - DARK MYSTERY - 5/1 > 3/1 > 2/1 > 1/1
first numbers is a time 2-10
Then a horses name Dark Mystery
Then a set of betting odds 5/1 > 3/1 > 2/1 > 1/1 (this line of odds can be just one set
ie 5/1
upto 7 sets
ie 5/1 > 3/1 > 2/1 > 1/1 > 8/13 > 1/2 > 4/11

I would also add that the info would start in cell A1 and can run down into cell A150 so I would need to able to copy down the formula per line.

So I would like it set out that if the info is in Col A1 then it would follow that in

Col B1 = time
Col C1 = Horse Name
Col D1 = Odds

May I also ask if it would be possible to change the time layout to a decimal thus instead of 2-10 it would read 2.10

Many thanks in advance for any help provided as it will be much appreciated and I will be sure to provide feedback as is neccessary. Thanks again.

Regards

PS Image attached also for interest.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	2-10 - DARK MYSTERY - 5/1 > 3/1 > 2/1 > 1/1	2.10	DARK MYSTERY	5/1 > 3/1 > 2/1 > 1/1
Data

Cell Formulas
Range	Formula
B1:D1	B1	=SUBSTITUTE(TEXTSPLIT(A1," -"),"-",".")
Dynamic array formulas.

 

[Celticshadow]

How about

Fluff.xlsm
	A	B	C	D	E
1	2-10 - DARK MYSTERY - 5/1 > 3/1 > 2/1 > 1/1	2.10	DARK MYSTERY	5/1 > 3/1 > 2/1 > 1/1
Data

Cell Formulas
Range	Formula
B1:D1	B1	=SUBSTITUTE(TEXTSPLIT(A1," -"),"-",".")
Dynamic array formulas.

Hi Fluff

Many thanks for replying it is much appreciated. I am delighted to say that your solution works an absolute treat and once again I amazed at the knowledge that is so freely shared by yourself and other forum members. Many many thanks for your considered help.

Regards

 

[Fluff]

Glad to help & thanks for the feedback.

 

[OaklandJim]

Using my approach you'll need a custom function in VBA. Hopefully you know how to do that.

Function ExtractName(psText)

    Dim iChar As Long
    
    Dim sName As String
    
    Dim sChar As String
    
    Dim bIsAlpha As Boolean
    
    For iChar = 1 To Len(psText)
        
        sChar = Mid(psText, iChar, 1)
        
        bIsAlpha = Not IsNumeric(sChar) _
               And Not sChar = "-" _
               And Not sChar = ">" _
               And Not sChar = "/"
               
        If bIsAlpha Then sName = sName & sChar
               
    Next iChar
    
    ExtractName = Trim(sName)
    
End Function

Odds.xlsm
	A	B	C	D
1		Time	Name	Odds
2	2-10 - DARK MYSTERY - 5/1 > 3/1 > 2/1 > 1/1	2:10	DARK MYSTERY	- 5/1 > 3/1 > 2/1 > 1/1
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUBSTITUTE(LEFT(A2,FIND("~",SUBSTITUTE(A2,"-","~",2))-1),"-",":")
C2	C2	=ExtractName(A2)
D2	D2	=TRIM(MID(A2, (SEARCH(C2,A2,1)+LEN(C2)), LEN(A2)- (SEARCH(C2,A2,1)+LEN(C2) -1) ) )

 

[OaklandJim]

I misread your post. The odds should not include the leading dash.

Odds.xlsm
	A	B	C	D
1		Time	Name	Odds
2	2-10 - DARK MYSTERY - 5/1 > 3/1 > 2/1 > 1/1	2:10	DARK MYSTERY	5/1 > 3/1 > 2/1 > 1/1
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUBSTITUTE(LEFT(A2,FIND("~",SUBSTITUTE(A2,"-","~",2))-1),"-",":")
C2	C2	=ExtractName(A2)
D2	D2	=MID(A2, (SEARCH(C2,A2,1)+LEN(C2)+2), LEN(A2)- (SEARCH(C2,A2,1)+LEN(C2) -1) )