Extract month

[sythong]

Hi all


Need help to extract a month and year from a consolidated invoice number
and date in a single cell:-

10-00001-02/01/10

10-00001 is the invoice number
02-01/10 is the date

Any suggestion on how to get the month is appreciated.

Thanks

 

[Ganjin]

okay, assuming the date is in the format of Month, Day, Year:
<TABLE style="WIDTH: 196pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=261 border=0><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=122 height=20>10-00001-02/01/10</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>MARCH</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>2010</TD></TR></TBODY></TABLE>
B1 formula:
=TEXT(RIGHT(A1,8),"MMMM")

C1 formula:
=TEXT(RIGHT(A1,8),"YYYY")

 

[delaneyjm]

If you are looking for a text version of the month, you could try this formula (albeit, someone may suggest a better formula):

=TEXT(--RIGHT(SUBSTITUTE(A1,"-","|",2),LEN(A1)-FIND("|",SUBSTITUTE(A1,"-","|",2))),"mmm")

 

[barry houdini]

Is 02 the month? If so try

=LEFT(RIGHT(A1,8),2)+0

 

[sythong]

Thanks to Ganjin, delaneyjm and barry for the quick reply.

For Ganjin and delaney the result is correct. Barry the 02 is the date
as we follow dd/mm/yy format. Please advise how to substitute to get
month instead of day. Thanks