# Mapping strings to integers

W

#### Walter Briscoe

This is the inverse need of my question "Mapping integers to strings" in
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")

C

#### 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

C

#### 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

C

#### 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

W

#### 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).

C

#### 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