Extracting month from a list of dates

[Aviator74]

Hi

I have a long list of dates in a column in the format below, some of them start with a space. I'm struggling to find a formula which will extract only the month in a separate column (e.g. "July" in the first row). Any suggestions?

[TABLE="width: 160"]
<tbody>[TR]
[TD]14 July 2014][/TD]
[/TR]
[TR]
[TD] 9 June 2014][/TD]
[/TR]
[TR]
[TD]14 August 2014][/TD]
[/TR]
[TR]
[TD] 1 April 2014][/TD]
[/TR]
[TR]
[TD]22 July 2014][/TD]
[/TR]
[TR]
[TD] 5 September 2014][/TD]
[/TR]
[TR]
[TD]20 January 2014][/TD]
[/TR]
[TR]
[TD]20 August 2014][/TD]
[/TR]
[TR]
[TD] 5 September 2014]

Thanks
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Oeldere]

try this (untested)

b1=if(left(a1)="",month(right(a1),len(a1)-1)),month(a1))

 

[ukmikeb]

Hi

Try in B1 :-

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",10)),3,20))

hth

 

[Momentman]

I am not sure if the brackets at the end of each line are actually there,

Well, I assumed they are

Excel 2010
	A	B
1	14 July 2014]	July
2	9 June 2014]	June
3	14 August 2014]	August
4	1 April 2014]	April
5	22 July 2014]	July
6	5 September 2014]	September
7	20 January 2014]	January
8	20 August 2014]	August
9	5 September 2014]	September
Sheet4

Cell Formulas
Range	Formula
B1	=TEXT(TRIM(LEFT(A1,LEN(A1)-1)),"mmmm")

 

[Aviator74]

try this (untested)

b1=if(left(a1)="",month(right(a1),len(a1)-1)),month(a1))

Thanks

Unfortunately I get a message back saying there's too many arguments

 

[Aviator74]

Thanks Momentman/ukmikeb

Your suggested formulas work fine for the cells that don't start with a space. Can they be amended to incorporate the cells that start with a space?

 

[Aviator74]

Update

I've used the TRIM function to get rid of any spaces at the start, I now get what I need

Thanks for your contributions

 

[Oeldere]

Thanks Momentman/ukmikeb

Your suggested formulas work fine for the cells that don't start with a space. Can they be amended to incorporate the cells that start with a space?

I noticed my formula won;t work (sorry for that).

But are you tested the formula of momentman, since that would trim the space (and not tested) should work?

Update

I've used the TRIM function to get rid of any spaces at the start, I now get what I need

Thanks for your contributions

Please add your solution on the forum, so other members (including me) can learn from it.

 

[Aviator74]

Sure.

I used =TRIM(A1) on column A which cleared any starting spaces, the results were placed in column B. I then used =TEXT(TRIM(LEFT(B1,LEN(B1)-1)),"mmmm") in column C which extracted the month name only.

Hope this helps.

 

[Momentman]

Sure.

I used =TRIM(A1) on column A which cleared any starting spaces, the results were placed in column B. I then used =TEXT(TRIM(LEFT(B1,LEN(B1)-1)),"mmmm") in column C which extracted the month name only.

Hope this helps.

This the same formula I posted in post #4, i guess the edited version came in same time with your post so you didn't notice that i had updated it