Change Month (words) to (numbers)

I have a column with the month as the full word (January or February, etc.).
I need to translate the words to numbers in a separate column (1 or 2, etc.)
I haven't been able to find a function that can help perform this task. "If"
statements will not go further than 8 levels. The month function will not
recognize it because it's not part of a full date. What other options do I
have? I feel like I'm missing something obvious here...

Thank you,
Cathy

Cathy -- Unless some of the gurus have an easier idea, you might need to go
with a VLOOKUP function. Basically pretty easy with good documentation.

K L
1 January 1
2 February 2
3 March 3
etc

Then, in your data

A
1 March =VLOOKUP(A1,$K$1:$L$12,2,false)

and that should return the right month number.

HTH

"Cathy" wrote:

> I have a column with the month as the full word (January or February, etc.).
> I need to translate the words to numbers in a separate column (1 or 2, etc.)
> I haven't been able to find a function that can help perform this task. "If"
> statements will not go further than 8 levels. The month function will not
> recognize it because it's not part of a full date. What other options do I
> have? I feel like I'm missing something obvious here...
>
> Thank you,
> Cathy

There is a function called CHOOSE, as far as I remember. This will do
the trick.

Darlove (PL)

Hi

Several ways exist:

1. Convert the month name to date, and calculate month number from it - like
=MONTH(DATEVALUE("01." & A1 & "." & YEAR(TODAY())))
(NB! use valid for your regional settings date string format)

2. Use CHOOSE function - like
=CHOOSE(A1,{"Yanuary";"February";"March";"April";"May";"June";"July";"August
";"September";"October";"November";"December"},0)

3. Use a lookup table (MonthName, MonthNumber) and VLOOKUP to find a month
number from there - like
=VLOOKUP(A1,LookupTable!$A$2:$B$13,2,0)

4. Use VLOOKUP with an array argument - like
=VLOOKUP(A1,{"January";1,"February";2, ... , "December";12},2,0)
(NB! you musty check delimiters yourself - I have different regional
settings, so the last example is untested)


Arvi Laanemets



"Cathy" <Cathy@discussions.microsoft.com> wrote in message
news:D9827422-EDBB-471F-993D-3B5A709F7928@microsoft.com...
> I have a column with the month as the full word (January or February,
etc.).
> I need to translate the words to numbers in a separate column (1 or 2,
etc.)
> I haven't been able to find a function that can help perform this task.
"If"
> statements will not go further than 8 levels. The month function will not
> recognize it because it's not part of a full date. What other options do
I
> have? I feel like I'm missing something obvious here...
>
> Thank you,
> Cathy

Arvi Laanemets wrote...
>Several ways exist:

Indeed.

>1. Convert the month name to date, and calculate month number from it - like
>=MONTH(DATEVALUE("01." & A1 & "." & YEAR(TODAY())))
>(NB! use valid for your regional settings date string format)
....

As long as the OP's language is English and the month names are spelled
correctly, both

=MONTH("1 "&A1)

and

=MONTH(A1&" 1")

return the month number for A1 no matter what the system date formats
may be.

Or even better solution. Paste this code into a module in your file (or
the Personal.xls to have access to this function in any of your
workbooks):

Public Function MonthToNumber(stMonthName As String) As Variant
Select Case LCase(stMonthName)
Case "january": MonthToNumber = 1
Case "february": MonthToNumber = 2
Case "march": MonthToNumber = 3
Case "april": MonthToNumber = 4
Case "may": MonthToNumber = 5
Case "june": MonthToNumber = 6
Case "july": MonthToNumber = 7
Case "august": MonthToNumber = 8
Case "september": MonthToNumber = 9
Case "october": MonthToNumber = 10
Case "november": MonthToNumber = 11
Case "december": MonthToNumber = 12
Case Else: MonthToNumber = CVErr(xlErrValue)
End Select
End Function

You will be able to do sth like this: =MonthToNumber(C2) and will be
given the right number of the month instantly. You will be able to
choose the function from the Functions dialog box. It will appear as a
User Defined Function. Hope this is helpful.

Darlove (PL)

darlove wrote...
>Or even better solution. Paste this code into a module in your file (or
>the Personal.xls to have access to this function in any of your
>workbooks):
....

Why better?

To use udfs, macro security must be set to medium or low, or the OP
must 'sign' the macros in his/her Personal.xls workbook.

Also, udfs are much slower than built-in functions.

Your particular udf involves more typing than is needed. Compare

=MonthToNumber(C2)

to

=MONTH("1 "&C2)

Finally, your udf should have called Trim to ensure there are no
leading or trailing spaces in the Select Case block.

Harlan Grove napisal(a):
> darlove wrote...
> >Or even better solution. Paste this code into a module in your file (or
> >the Personal.xls to have access to this function in any of your
> >workbooks):
> ...
>
> Why better?

Why better?
Here's why.
1. You do not have to remember your formula. It is easier to remember
MonthToNumber, don't you think? You write MonthToNumber(c2) and
immediately know what that means.
2. You can tailor the function so that is behaves the way you really
want it to and what is even more important: if you need to make a
change later on you do it IN ONE PLACE instead of having to go through
all the sheets that contain the formula. Isn't it nice? Think how
time-consuming it would be to have to go over the spreadsheets and
correct the formula you have written.
3. This function is REALLY fast. Try it and you will see for yourself
I would be very surprised if you needed something that should be
faster than this. It is true that VBA proc are slower than the built-in
functions, no question about it, but everything depends on what
requirements you have as to the particular task in mind. I don't think
she needed anything better than the function I presented. If I am
wrong, sorry then

One thing I forgot to add. If you put the function in a module in
Personal.xls, be sure to add a reference to this workbook in order to
be able to use the function without qualifying it with the workbook's
name, like =Personal.xls!MonthToNumber(a1). In the VBA Editor go to
Tools -> References and tick the Personal option. Or, of course, you
can add the function to a module in the workbook you are working on and
then do nothing apart from using it as you would use a standard
built-in function.

Now, I think I have convinced you of the obvious fact that it is much
much better to use a VBA formula than not. If you do not feel
satisfied, well, I believe I can't help it any more

Kind regards
Darlove (PL)

Generally, I would go with the VBA method because of its inherent versatility and "portability" through the entire workbook.

But then again I am comfortable with VBA and enjoy the process of creating macros/procedures/functions, etc.
(Please withhold remarks referring to my mental stability [I freely admit I am insane, which makes me sane because I realize I am insane -- Catch-22], sexual proclivities [masochism is a mentally based dysfunction -- see previous], or my ancestry [in-breeding causes mental illnes!].

However, since it appears the initial poster is using formulae, she apparently is comfortable with formulae, but is she comfortable with VBA? I do not think most Excel users are, probably the majority of Excel users do not even know about VBA.

Therefore, allow me to weigh in for the side of the formulaic approach in this regard.

I relinquish the soapbox for another.
Thank-you

> Here's why.
> 1. You do not have to remember your formula. It is easier to remember
> MonthToNumber, don't you think? You write MonthToNumber(c2) and
> immediately know what that means.
> 2. You can tailor the function so that is behaves the way you really
> want it to and what is even more important: if you need to make a
> change later on you do it IN ONE PLACE instead of having to go through
> all the sheets that contain the formula. Isn't it nice? Think how
> time-consuming it would be to have to go over the spreadsheets and
> correct the formula you have written.
> 3. This function is REALLY fast. Try it and you will see for yourself
> I would be very surprised if you needed something that should be
> faster than this. It is true that VBA proc are slower than the built-in
> functions, no question about it, but everything depends on what
> requirements you have as to the particular task in mind. I don't think
> she needed anything better than the function I presented. If I am
> wrong, sorry then
>
> One thing I forgot to add. If you put the function in a module in
> Personal.xls, be sure to add a reference to this workbook in order to
> be able to use the function without qualifying it with the workbook's
> name, like =Personal.xls!MonthToNumber(a1). In the VBA Editor go to
> Tools -> References and tick the Personal option. Or, of course, you
> can add the function to a module in the workbook you are working on and
> then do nothing apart from using it as you would use a standard
> built-in function.
>
> Now, I think I have convinced you of the obvious fact that it is much
> much better to use a VBA formula than not. If you do not feel
> satisfied, well, I believe I can't help it any more
>
> Kind regards
> Darlove (PL)


I would say that the only reason for a UDF is if there is no other solution
or if the other solution is very complex, neither of which would fit this
case. Also think of the consequences if the OP needs to share the workbook,
that means the UDF has to be distributed as well.

Peo

darlove wrote...
....
>Why better?
>Here's why.
>1. You do not have to remember your formula. It is easier to remember
>MonthToNumber, don't you think? You write MonthToNumber(c2) and
>immediately know what that means.

No, I don't think it's easier to *remember* the udf. There are several
hundred built-in functions already, so adding even more udfs have
limited utility. It may be easier to recognize that the udf does if its
name is sufficiently long.

And while you may know what its supposed to produce, your particular
implementation is flawed because you failed to trim off
leading/trailing spaces. That's not a problem for the formula approach.

So there's a trade-off between recognizability of function and
robustness, at least when considering your flawed implementation.

>2. You can tailor the function so that is behaves the way you really
>want it to and what is even more important: if you need to make a
>change later on you do it IN ONE PLACE instead of having to go through
>all the sheets that contain the formula. Isn't it nice? Think how
>time-consuming it would be to have to go over the spreadsheets and
>correct the formula you have written.

Formulas can't be tailored?

As for making changes only once, the logical extreme would be to make
all formulas involving anything more complicated than single function
calls with all simple arguments into udfs. The drawback would be
substantial increase in recalculation time.

And if it's easy to write pointless udfs, it's not all that difficult
to write general search and replace macros to make the task of batch
editing pretty simple.

>3. This function is REALLY fast. Try it and you will see for yourself
....

It's really simple, so it should be fairly quick. But it'll be a lot
slower than using only built-in functions. And if macro security is set
to high, it'll very quickly return #NAME? errors. That won't happen
using only built-in functions.

>Now, I think I have convinced you of the obvious fact that it is much
>much better to use a VBA formula than not. If you do not feel
>satisfied, well, I believe I can't help it any more

No, you've just presented for yet another time the shallow rationale of
someone with little experience developing spreadsheets many people use.
udfs should only be used when necessary because the same functionality
can't be achieved using formulas and built-in functions in a reasonable
number of cells or in the very rare situations in which udfs would be
faster (when non-udf formulas involve massively redundant calculations).

Seems to me that of all the arguments presented, it all boils down to
1. personal preference - go with what you are comfortable with
2. personal knowledge - go with what you know
3. if the solution works, who cares? - to goal is a solution to the problem, not the solution method