Mapping strings to integers

[Walter Briscoe]

This is the inverse need of my question "Mapping integers to strings" in
<http://groups.google.com/g/7097feea/t/3b458343e881c994/d/7e69c03bea746b
4b>
I want to map English language month names to month numbers in 1..12.

So far, I have
=VLOOKUP($AL9,{"April",4;"August",8;"December",12;"February",2;"January"
,1;"July",7;"June",6;"March",3;"May",5;"November",11;"October",10;"Septe
mber",9},2,FALSE)

=HLOOKUP($AL9,{"April","August","December","February","January","July","
June","March","May","November","October","September";4,8,12,2,1,7,6,3,5,
11,10,9},2,FALSE)
and

=MATCH($AL9,{"January","February","March","April","May","June","July","A
ugust","September","October","November","December"},0)

AL9 contains a month name.

These formulas seem inappropriately complicated for mapping month names
to month numbers, but are general purpose for mapping arbitrary sets of
strings to numbers.

Is there a simpler MonthNumber formula, I have missed?

I now answer my own question: =MONTH("1-" & $AL9 & "-1900")

 

[Claus Busch]

Hi Walter,

Am Thu, 27 Jun 2013 09:39:31 +0100 schrieb Walter Briscoe:

AL9 contains a month name.

These formulas seem inappropriately complicated for mapping month names
to month numbers, but are general purpose for mapping arbitrary sets of
strings to numbers.

try:
=MATCH(AL9,TEXT(ROW(1:12)*28,"MMMM"),0)


Regards
Claus Busch

 

[Claus Busch]

Hi Walter,

Am Thu, 27 Jun 2013 10:51:33 +0200 schrieb Claus Busch:

try:
=MATCH(AL9,TEXT(ROW(1:12)*28,"MMMM"),0)

I don't know if this will work in a english excel version:
=MONTH(AL9&1)


Regards
Claus Busch

 

[Claus Busch]

Hi Walter,

Am Thu, 27 Jun 2013 10:51:33 +0200 schrieb Claus Busch:

=MATCH(AL9,TEXT(ROW(1:12)*28,"MMMM"),0)

this formula is to enter with CTRL+Shift+Enter


Regards
Claus Busch

 

[Ron Rosenfeld]

I now answer my own question: =MONTH("1-" & $AL9 & "-1900")

The 1900 is superfluous.
Actually, =MONTH(1&$AL9) should work also.

 

[Walter Briscoe]

Thanks, Claus.

In message <[email protected]> of Thu, 27 Jun 2013 11:02:54
in microsoft.public.excel.worksheet.functions, Claus Busch

Hi Walter,

Am Thu, 27 Jun 2013 10:51:33 +0200 schrieb Claus Busch:

You noted in a second post that this needs to be an array formula
and so must be completed with Ctrl+Shift+Enter so it appears as
{=MATCH(AL9,TEXT(ROW(1:12)*28,"MMMM"),0)}

I experimented with it with copying down and found, that, as written,
1:12 became 2:13, etc.
=MATCH(AL9,TEXT(ROW($1:$12)*28,"MMMM"),0)
works without problem.

I don't know if this will work in a english excel version:
=MONTH(AL9&1)

It does!

Regards
Claus Busch

I am going to use Tools\Formula Auditing\Evaluate Formula to show the
evaluations of
{=MATCH(A12,TEXT(ROW($1:$12)*28,"MMMM"),0)}
and
=MONTH(A12&1)
as I find both instructive. A12 contains "December".
Is there any easy way to transcribe such information in 2003?
I solemnly typed what is written below.

MATCH(A12,TEXT(ROW($1:$12)*28,"MMMM"),0)

' Evaluate A12
MATCH("December",TEXT(ROW($1:$12)*28,"MMMM"),0)

' ROW($1:$12) evaluates as array constant {1;2;3;4;5;6;7;8;9;10;11;12}
MATCH("December",TEXT({1;2;3;4;5;6;7;8;9;10;11;12}*28,"MMMM"),0)

' 28 is multiplied in to form array constant {28;56;84; ... 280;308;336}
MATCH("December",TEXT({28;56;84; ... 336},"MMMM"),0)

' TEXT is applied toan array constant
MATCH("December", {"January";"February";"March"; ... "December"},0)

' Get relative position of "December" in {"January"; ... "December"},0)
12

The second formula is significantly simpler in both text and execution.

MONTH(A12&1)

' Evaluate A12
MONTH("December"&1)

' Evaluate concatenation operator
MONTH("December 1")

' Evaluate MONTH.
' I think "December 1" is equivalent to "December 1, 1900".
' Excel ignores the local date format here - "1 December".
12

I am very grateful you could change =MONTH("1-" & A12 & "-1900")
to =MONTH(A12&1).

 

[Claus Busch]

Hi Walter,

Am Thu, 27 Jun 2013 13:09:07 +0100 schrieb Walter Briscoe:

I experimented with it with copying down and found, that, as written,
1:12 became 2:13, etc.
=MATCH(AL9,TEXT(ROW($1:$12)*28,"MMMM"),0)
works without problem.

I didn't know that you wanted to copy down the formula, my bad.

I am going to use Tools\Formula Auditing\Evaluate Formula to show the
evaluations of
{=MATCH(A12,TEXT(ROW($1:$12)*28,"MMMM"),0)}
and
=MONTH(A12&1)
as I find both instructive. A12 contains "December".
Is there any easy way to transcribe such information in 2003?
I solemnly typed what is written below.

I find no way. The macro recorder doesn't record anything and into the
dialog I can't copy anything.


Regards
Claus Busch