Left / right...?

[How_Do_I]

Hi all,

I have years in the format below with the "-" between the two...

I need the formula to put 09 in Q6 and 10 in Q7 please... If I used 2006 - 2007 I'd need the 06 and 07 in the respective cells.

Excel Workbook
	Q
5	2009 - 2010
6	9
7	10
All Seasons

 

[JackDanIce]

Something like:
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 65px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>2009 - 2010</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">09</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">10</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=RIGHT(LEFT(A1,FIND("-",A1)-2),2)</TD></TR><TR><TD>A3</TD><TD>=RIGHT(RIGHT(A1,LEN(A1)-FIND("-",A1)-1),2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

(PS don't get your hopes up for Wednesday night!!)

 

[baodinh]

Q6: =mid(Q5,3,2)
Q7: =right(Q5,2)

 

[How_Do_I]

Something like:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 8pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 65px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>2009 - 2010</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">09</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">10</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=RIGHT(LEFT(A1,FIND("-",A1)-2),2)</TD></TR><TR><TD>A3</TD><TD>=RIGHT(RIGHT(A1,LEN(A1)-FIND("-",A1)-1),2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

(PS don't get your hopes up for Wednesday night!!)

Thank for the formulas.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Don't worry about Wednesday night, as being the team that have taken the most points off Sir Alex… we'll roll on our backs and let United tickle our stomachs!!! That said, I'm very close to seeking the odds for Torres NEVER to score again in his career!

 

[How_Do_I]

Q6: =mid(Q5,3,2)
Q7: =right(Q5,2)

Thanks, is it me or should both these solution and the ones above come out as text and not a number?

 

[JackDanIce]

5-1 when I last looked for FT to not score rest of the season, I'm tempted by them too!

Glad it worked; baodinh's formuale are simpler/easier, but I was trying to use the recognition of the "-" in your format and then I realised you only wanted the last two numbers of each year, so wrapped my originals within a RIGHT function

 

[JackDanIce]

They come out as text for me. If they weren't then the "09" result would show as just "9"

 

[baodinh]

If you want a number, just wrap a value() around the formula.

 

[How_Do_I]

Thanks Jack, I need the 09 so I'll have to work with the fact that it is text.

 

[How_Do_I]

Hello again all...

If I have the following what formula can I use to get the data in order... i.e. 2010 - 2011 at the top please?

Excel Workbook
	H	I
19	2009 - 2010	10
20	2008 - 2009	09
21	2007 - 2008	08
22	2006 - 2007	07
23	2010 - 2011	11
All Seasons