How to extract month from a date that's in Text format?

[sudarshanrao]

I have a column with the following values:
[TABLE="width: 309"]
<tbody>[TR]
[TD]SubmitDate[/TD]
[/TR]
[TR]
[TD]November 8, 2012 1:37:07 PM GMT+05:30[/TD]
[/TR]
[TR]
[TD]November 8, 2012 9:32:03 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]November 8, 2012 4:32:54 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]November 7, 2012 10:28:11 PM GMT+05:30[/TD]
[/TR]
[TR]
[TD]November 7, 2012 8:36:43 PM GMT+05:30[/TD]
[/TR]
[TR]
[TD]November 7, 2012 6:33:13 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]November 1, 2012 4:14:46 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]November 1, 2012 12:46:45 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 31, 2012 5:43:29 PM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 31, 2012 1:06:28 PM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 25, 2012 2:06:48 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 25, 2012 1:08:07 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 22, 2012 4:02:06 PM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 19, 2012 4:16:04 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 19, 2012 4:12:50 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 12, 2012 6:19:03 PM GMT+05:30[/TD]
[/TR]
[TR]
[TD]October 12, 2012 8:30:15 AM GMT+05:30[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need another column, with just the month specified there based on above dates, so that I can manipulate the complete sheet further using a pivot. Some formatting issue isn't allowing me to use month() function, to derive the month of the date mentioned in the cell.
Using Auto-filter is too tedious, since I'd have to do it 12 times to cover each of the month. Tried recording a macro, but that too isn't helping much, since it gets specific to cells and hence isn't re-usable in another sheet with similar data.
How can I automate the task of extracting just the month from the above column?

Thanks,
Sudarshan

 

[VoG]

Try

=LEFT(A1,FIND(" ",A1)-1)

 

[sudarshanrao]

Wow, that works! But then I'd have one more request - I need to be able to sort it based on month, while I do a pivot. So is there a way to put it like -
01 Jan
02 Feb
Or any way where I can list it chronologically?

Thanks,
Sudarshan

 

[VoG]

Try

=DATEVALUE("1-"&LEFT(A1,FIND(" ",A1)-1))

and format as a Date.

 

[sudarshanrao]

Perfect! Thank you so much!