Seperate team name and odds to 2 seperate cells

ronie85

New Member
Joined
Jan 25, 2014
Messages
44

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
ronie85,

Try....
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Aston Villa 100/30</td><td style=";">Aston Villa</td><td style=";">100/30</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Draw 13/1</td><td style=";">Draw</td><td style=";">13/1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=TRIM(<font color="Blue">SUBSTITUTE(<font color="Red">B6,D6,""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">B6," ",REPT(<font color="Purple">" ",10</font>)</font>),10</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Hope that helps.
 
Last edited:

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Text >>>></td><td style=";">Aston Villa (100/30)</td><td style=";">Aston Villa </td><td style=";">(100/30)</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Text >>>></td><td style=";">Draw 13/1</td><td style=";">Draw </td><td style=";">13/1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Hyperlink >>>>></td><td style="text-decoration: underline;color: #0000FF;;">QPR (17/10)</td><td style=";">QPR </td><td style=";">(17/10)</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=Split_It(<font color="Blue">B6,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=Split_It(<font color="Blue">B6,2</font>)</td></tr></tbody></table></td></tr></table><br />

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

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,914
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top