Converting month name to a number

[azizrasul]

I have a column of data that includes month names for 1 year which do not appear in the correct order i.e. Jan, Feb, Mar etc.

In an adjacent column I want to have a formula that will give me the month number so that I can sort the column in the correct monthly order.

The reason behind getting the correct order is that I have another column containing some numbers which I want manipulate in code.

 

[VoG]

Try

=MONTH(1&A1)

 

[Pauljj]

I don't think you will need to give them numbers.

Go to Data and Sort then click options on first key sort order, change it to months and this will allow you to sort the months into the correct order

 

[Pauljj]

If you want to sort the months into an order not in the normal calendar sequence you could always create a list and then sort

 

[azizrasul]

=MONTH(1&A1) worked.

Pauljj, I couldn't understand what you meant by

... change it to months ...

 

[Pauljj]

If you go to data sort then click options then click on First key sort order, you should get various choices, one of those being months of the year

 

[azizrasul]

The choices I get when I press options are

Sort top to bottom
Sort left to right

 

[Peter_SSs]

The choices I get when I press options are

Sort top to bottom
Sort left to right

If that is so, you are probably using a recent version of Excel. In the Sort dialog, click the 'Order' drop-down and choose 'Custom List..' where a couple of the options should relate to month names. Choose the appropriate one for your data.

 

[azizrasul]

OK I tried that, but it still didn't work. If I go to a new tab and enter

MTHNAME
May
April
July
June

it still didn't work?

 

[Peter_SSs]

What version of Excel are you using?

What cells did you typoe this into?
MTHNAME
May
April
July
June