Change mmm to mmmm ie Mar to March

[drawing.blanks]

Hi,


I am trying to change the value of a cell which contains the name of the month in Mmm format into Mmmm format.

I would like to to this without using the vlookup function.

Any help would be appreciated.

ie: cell A1: Nov
cell B1: would show November
if the value in A1 is Dec, B1 therefore will be December
Thx

[Paul]

Is the value in A1 a date value, e.g. 11/1/2010, or a text string like "Nov"?

If it's a date value, use:

=text(a1,"mmmm")

If it's text, try:

=TEXT(DATE(1900,LOOKUP(A1,{"apr","aug","dec","feb","jan","jul","jun","mar","may","nov","oct","sep"},{4,8,12,2,1,7,6,3,5,11,10,9}),1),"mmmm")

or

=LOOKUP(A1,{"apr","aug","dec","feb","jan","jul","jun","mar","may","nov","oct","sep"},{"April","August","December","February","January","July","June","March","May","November","October","September"})

Note in the month arrays, they are in alphabetical order.

[shg]

Another way, if text:

=TEXT(A1 & "0", "mmmm")

[drawing.blanks]

Thanks both Paul & Shg. It works perfectly.