Extracting month from a list of dates

Aviator74

New Member
Joined
Sep 8, 2014
Messages
9
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]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I am not sure if the brackets at the end of each line are actually there,

Well, I assumed they are


Excel 2010
AB
114 July 2014]July
29 June 2014]June
314 August 2014]August
41 April 2014]April
522 July 2014]July
65 September 2014]September
720 January 2014]January
820 August 2014]August
95 September 2014]September
Sheet4
Cell Formulas
RangeFormula
B1=TEXT(TRIM(LEFT(A1,LEN(A1)-1)),"mmmm")
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Code:
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?


Code:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,224,386
Messages
6,178,285
Members
452,835
Latest member
ExcelNerd24

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top