Advanced Sort/Filter function...

[ScreaminPain]

I'm hoping this is readable... I"m having difficulty using the "advanced" sort and filter for this data. Notice below the SCR heading the results are listed as letter and then the actual score, eg: W 7-2, or L 3-6 etc. I've tried using "text to column" function to separate the letter value from the actual score to no avail. Excel correctly separates the data, but reads the score numbers as a date and I can't seem to format it any differently.

Is there an EXCEL formula that will allow me to sort based upon ONLY the letter value, regardless of the following score?

Thx for any help....

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 68px"><COL style="WIDTH: 58px"><COL style="WIDTH: 46px"><COL style="WIDTH: 152px"><COL style="WIDTH: 120px"><COL style="WIDTH: 52px"><COL style="WIDTH: 81px"><COL style="WIDTH: 58px"><COL style="WIDTH: 47px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">Vs</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">Score</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">Away Starter</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">Home Starter</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">ATL Line</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">O/U</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">ERA</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #666699; COLOR: #ffffff">RTG</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">8/10/2011</TD><TD style="FONT-SIZE: 10pt">@ CHC</TD><TD style="FONT-SIZE: 10pt">L 2-4</TD><TD style="FONT-SIZE: 10pt">Ross Detwiler (L)</TD><TD style="FONT-SIZE: 10pt">Rodrigo Lopez (R)</TD><TD style="FONT-SIZE: 10pt">L 101</TD><TD style="FONT-SIZE: 10pt">U 9 -123</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4.60</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">192</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 10pt">8/9/2011</TD><TD style="FONT-SIZE: 10pt">@ CHC</TD><TD style="FONT-SIZE: 10pt">W 3-1</TD><TD style="FONT-SIZE: 10pt">Chien-Ming Wang (R)</TD><TD style="FONT-SIZE: 10pt">Matt Garza (R)</TD><TD style="FONT-SIZE: 10pt">W 142</TD><TD style="FONT-SIZE: 10pt">U 9.5 -124</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3.34</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">34</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 10pt">8/7/2011</TD><TD style="FONT-SIZE: 10pt">@ COL</TD><TD style="FONT-SIZE: 10pt">W 3-2</TD><TD style="FONT-SIZE: 10pt">John Lannan (L)</TD><TD style="FONT-SIZE: 10pt">Aaron Cook (R)</TD><TD style="FONT-SIZE: 10pt">W 115</TD><TD style="FONT-SIZE: 10pt">U 10 111</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4.43</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">229</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 10pt">8/6/2011</TD><TD style="FONT-SIZE: 10pt">@ COL</TD><TD style="FONT-SIZE: 10pt">L 7-15</TD><TD style="FONT-SIZE: 10pt">Livan Hernandez (R)</TD><TD style="FONT-SIZE: 10pt">Jhoulys Chacin (R)</TD><TD style="FONT-SIZE: 10pt">L 155</TD><TD style="FONT-SIZE: 10pt">O 9.5 104</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4.55</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">45</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 10pt">8/5/2011</TD><TD style="FONT-SIZE: 10pt">@ COL</TD><TD style="FONT-SIZE: 10pt">W 5-3</TD><TD style="FONT-SIZE: 10pt">Jordan Zimmermann (R)</TD><TD style="FONT-SIZE: 10pt">Juan Nicasio (R)</TD><TD style="FONT-SIZE: 10pt">W 123</TD><TD style="FONT-SIZE: 10pt">U 9 -113</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3.79</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">73</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 10pt">8/4/2011</TD><TD style="FONT-SIZE: 10pt">@ COL</TD><TD style="FONT-SIZE: 10pt">L 3-6</TD><TD style="FONT-SIZE: 10pt">Ross Detwiler (L)</TD><TD style="FONT-SIZE: 10pt">Esmil Rogers (R)</TD><TD style="FONT-SIZE: 10pt">L 122</TD><TD style="FONT-SIZE: 10pt">U 10.5 -111</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4.30</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">184</TD></TR></TBODY></TABLE>

 

[Jonmo1]

You can split it up with Text to columns
Choose Deliminated - Space
On the last screen, highlight the 2nd column (with the 2-4, 3-1 etc..)
And select "Text" in the column data format section.

 

[Jonmo1]

Otherwise, you can add a column to extract the W or L
Then sort on that column..

After sorting on column D (win/loss0

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Date	Vs	Score	win/Loss	Away Starter	Home Starter	ATL Line	O/U	ERA	RTG
2	#####	@ CHC	L 2-4	L	Ross Detwiler (L)	Rodrigo Lopez (R)	L 101	U 9 -123	4.6	192
3	#####	@ COL	L 7-15	L	Livan Hernandez (R)	Jhoulys Chacin (R)	L 155	O 9.5 104	4.55	45
4	#####	@ COL	L 3-6	L	Ross Detwiler (L)	Esmil Rogers (R)	L 122	U 10.5 -111	4.3	184
5	#####	@ CHC	W 3-1	W	Chien-Ming Wang (R)	Matt Garza (R)	W 142	U 9.5 -124	3.34	34
6	#####	@ COL	W 3-2	W	John Lannan (L)	Aaron Cook (R)	W 115	U 10 111	4.43	229
7	#####	@ COL	W 5-3	W	Jordan Zimmermann (R)	Juan Nicasio (R)	W 123	U 9 -113	3.79	73
Sheet1

Hope that helps.

 

[ScreaminPain]

Thank you, Jonmo1, for your response.

I'm assuming that your suggestions are the only solution. I"m able to achieve data separatation using the "text to columns" feature, but when separated, Excel reads the actual score as a date, dispite my formating to "text".

I was hoping I could create a formula that would sort without adding any additional columns.......