leojl
asked on
Excel month name to number
Hi..I have an Excel sheet with month in 3 letters..Jan Feb ...Jul etc
I wish to put the month number like
"01" for Jan and "12" for Dec etc in
a new column. What is a neat way to do this?
leo
I wish to put the month number like
"01" for Jan and "12" for Dec etc in
a new column. What is a neat way to do this?
leo
ASKER
hi bruintje,
thanks for the idea,what is the neater VBA way to do it please. leo
thanks for the idea,what is the neater VBA way to do it please. leo
OK,
took a shower and decided to make this anyway :O)
+go ALT+F11
+insert a new module
+paste this code
Public Function NameToNumber(ByRef cName As String) As String
Select Case UCase(cName)
Case Is = "JAN"
NameToNumber = "01"
Case Is = "FEB"
NameToNumber = "02"
Case Is = "MRT"
NameToNumber = "03"
Case Is = "APR"
NameToNumber = "04"
Case Is = "MAY"
NameToNumber = "05"
Case Is = "JUN"
NameToNumber = "06"
Case Is = "JUL"
NameToNumber = "07"
Case Is = "AUG"
NameToNumber = "08"
Case Is = "SEP"
NameToNumber = "09"
Case Is = "OCT"
NameToNumber = "10"
Case Is = "NOV"
NameToNumber = "11"
Case Is = "DEC"
NameToNumber = "12"
Case Is = ""
NameToNumber = ""
End Select
End Function
+back to the sheet
+choose insert function
+choose user defined
+then you can use your new function
+just click on the cell to evaluate with the "jan" in it...
+click ok and voila
HTH:O)Bruintje
took a shower and decided to make this anyway :O)
+go ALT+F11
+insert a new module
+paste this code
Public Function NameToNumber(ByRef cName As String) As String
Select Case UCase(cName)
Case Is = "JAN"
NameToNumber = "01"
Case Is = "FEB"
NameToNumber = "02"
Case Is = "MRT"
NameToNumber = "03"
Case Is = "APR"
NameToNumber = "04"
Case Is = "MAY"
NameToNumber = "05"
Case Is = "JUN"
NameToNumber = "06"
Case Is = "JUL"
NameToNumber = "07"
Case Is = "AUG"
NameToNumber = "08"
Case Is = "SEP"
NameToNumber = "09"
Case Is = "OCT"
NameToNumber = "10"
Case Is = "NOV"
NameToNumber = "11"
Case Is = "DEC"
NameToNumber = "12"
Case Is = ""
NameToNumber = ""
End Select
End Function
+back to the sheet
+choose insert function
+choose user defined
+then you can use your new function
+just click on the cell to evaluate with the "jan" in it...
+click ok and voila
HTH:O)Bruintje
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
looked at those date functions, but i guess it doesn't work with a string....
ah well if you translate that it should work :P
Hi leojl
I would change your months to dates and then format them as mmm to get "Jan", "Feb" etc then you can perform many types of time and date functions on your month names. To do this try this.
supposing your first month "Jan" is in cell A1 and "Dec" is in A12.
in cell A1 type 1/1/2000 then with the cell still selected go to Format>Cells>Number and select "Custom" now using any one of the pre-defined formats as a starting point type mmm
Now with A1 still selected LEFT click on the "fill handle" (little black square on the bottom right of the cell)and drag down to cell A12, now release the left mouse button and select "fill months".
Now in cell B1 type =MONTH(A1) and then simply copy this down.
The big advantage is you can now return many different types of aspects from you Months.
antrat
I would change your months to dates and then format them as mmm to get "Jan", "Feb" etc then you can perform many types of time and date functions on your month names. To do this try this.
supposing your first month "Jan" is in cell A1 and "Dec" is in A12.
in cell A1 type 1/1/2000 then with the cell still selected go to Format>Cells>Number and select "Custom" now using any one of the pre-defined formats as a starting point type mmm
Now with A1 still selected LEFT click on the "fill handle" (little black square on the bottom right of the cell)and drag down to cell A12, now release the left mouse button and select "fill months".
Now in cell B1 type =MONTH(A1) and then simply copy this down.
The big advantage is you can now return many different types of aspects from you Months.
antrat
It does work, i tried it before posting :-)
Actually, the Date Functions rely on regional settings, so there may araise some problems...
Actually, the Date Functions rely on regional settings, so there may araise some problems...
Oops sorry leojl
I should have said RIGHT mouse click for the fill drag.
antrat
I should have said RIGHT mouse click for the fill drag.
antrat
As you wanted "01", i complete my suggestion:
=TEXT(MONTH(DATEVALUE("1 " & A1 & " 1"));"00")
=TEXT(MONTH(DATEVALUE("1 " & A1 & " 1"));"00")
angelIII > it does indeed, had to translate the functions first....:O)
Ok I just spotted you wanted 01 for jan etc. In this case simply custom format your numeric month cells as 00
antrat
antrat
ASKER
I am overwhelmed by the outstanding solutions to my question.I did select
=MONTH(DATEVALUE("1 " & A1 & " 1"))
because it is so neat and easy to use.
But I did learn so much from the other
solutions. many thanks leo
=MONTH(DATEVALUE("1 " & A1 & " 1"))
because it is so neat and easy to use.
But I did learn so much from the other
solutions. many thanks leo
Well discarding the VBA way, which would be a bit smoother :O)
A kludge, excel seems to have problems putting more then 8 if then else nested formulas into a cell
so bit of a working workaround....
+in column A got the jan>dec values
+in column B i copied this formula into each cell 1 > 12
=IF(A1="Jan";"01";IF(A1="F
+in column C i copied down this formula
=IF(A7="jul";"07";IF(A7="a
+in column D i copied this one
=IF(B1<>"";B1;C1)
+which should give you the result you want.
you can make column B and C invisible with ... column > hide so you're left with 2 columns.
HTH:O)Bruintje