# Extracting month from a list of dates

#### Aviator74

##### New Member
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?

 14 July 2014] 9 June 2014] 14 August 2014] 1 April 2014] 22 July 2014] 5 September 2014] 20 January 2014] 20 August 2014] 5 September 2014] Thanks

<tbody>
</tbody>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
try this (untested)

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

Hi

Try in B1 :-
Code:
``=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",10)),3,20))``

hth

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:
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

Thanks Momentman/ukmikeb

Update

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

Code:
``````Thanks Momentman/ukmikeb

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

Last edited:
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.

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

Replies
2
Views
115
Replies
5
Views
179
Replies
22
Views
244
Replies
19
Views
482
Replies
2
Views
163

1,203,528
Messages
6,055,928
Members
444,835
Latest member

### 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.

### Which adblocker are you using?

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

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