Seperate team name and odds to 2 seperate cells

[ronie85]

I require a formula to give me the team name in one cell and the odds in another. Basically separate the letters and numbers. For the odds I require possibly an =right formula to give me everything on the right of the ‘space’ that is furthest right. I’m not sure how to explain getting the name separated, can someone help?

[TABLE="width: 78%"]
<tbody>[TR]
[TD]QPR 17/10
[/TD]
[TD]Draw 19/10
[/TD]
[TD]Stoke 9/5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aston Villa 100/30
[/TD]
[TD]Draw 13/5
[/TD]
[TD]Arsenal 4/5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Burnley 6/4
[/TD]
[TD]Draw 21/10
[/TD]
[TD]Sunderland 19/10
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Snakehips]

ronie85,

Try....

Excel 2007
	B	C	D
6	Aston Villa 100/30	Aston Villa	100/30
7	Draw 13/1	Draw	13/1
Sheet3

Cell Formulas
Range	Formula
C6	=TRIM(SUBSTITUTE(B6,D6,""))
D6	=TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(" ",10)),10))

Hope that helps.

 

[ronie85]

That formula works for;

[TABLE="width: 473"]
<tbody>[TR]
[TD]Leicester (19/4)
[/TD]
[TD]Draw (31/10)
[/TD]
[TD]Man Utd (4/6)
[/TD]
[/TR]
[TR]
[TD]Tottenham (8/15)
[/TD]
[TD]Draw (10/3)
[/TD]
[TD]West Brom (13/2)
[/TD]
[/TR]
</tbody>[/TABLE]

But when I put it in for

[TABLE="width: 652"]
<tbody>[TR]
[TD]QPR 17/10
[/TD]
[TD]Draw 19/10
[/TD]
[TD]Stoke 9/5
[/TD]
[TD]QPR 17/10
[/TD]
[/TR]
[TR]
[TD]Aston Villa 100/30
[/TD]
[TD]Draw 13/5
[/TD]
[TD]Arsenal 4/5
[/TD]
[TD]Aston Villa 100/30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
It returns the exact same contents

 

[Snakehips]

I did not realise that they were hyperlinks in your first post.

I'm pretty sure that you will not be able to split the 'displayed "friendly name' of a hyperlink without using vba.

 

[Snakehips]

Open the vba editor and copy the below code into a CODE Module.

You can then use it as worksheet function to split the end off either straight text or off the hyperlink 'friendly name'
Set second argument prt as 1 for the left part and to 2 for the right part.
eg

Excel 2007
	A	B	C	D
6	Text >>>>	Aston Villa (100/30)	Aston Villa	(100/30)
7	Text >>>>	Draw 13/1	Draw	13/1
8	Hyperlink >>>>>	QPR (17/10)	QPR	(17/10)
Sheet3

Cell Formulas
Range	Formula
C6	=Split_It(B6,1)
D6	=Split_It(B6,2)

Function Split_It(rng As Range, prt As Integer) As String
Dim Arry() As String
If rng.Hyperlinks.Count > 0 Then
Arry = Split(rng.Hyperlinks(1).TextToDisplay)
Else
Arry = Split(rng.Value)
End If
If Not prt = 1 Then
Split_It = Arry(UBound(Arry()))
Else
Arry(UBound(Arry())) = ""
Split_It = Join(Arry(), " ")
End If
End Function