convert text month to numeric month

S

salgud

I just asked here how to do the opposite. How do I convert "May" to 5
without using a case statement? I've looked all over on the net, but can't
find this specific thing.

Thanks in advance.
 
L

Lars-Åke Aspelin

I just asked here how to do the opposite. How do I convert "May" to 5
without using a case statement? I've looked all over on the net, but can't
find this specific thing.

Thanks in advance.
Click to expand...

Try this worksheet formula:

=SUMPRODUCT(ROW(1:12)*(TEXT(DATE(,ROW(1:12),1),"MMM")="May"))

Hope this helps / Lars-Åke
 
S

salgud

Try this worksheet formula:

=SUMPRODUCT(ROW(1:12)*(TEXT(DATE(,ROW(1:12),1),"MMM")="May"))

Hope this helps / Lars-Åke
Click to expand...

Sorry, should have been clear I want to do this in VBA, not in the
spreadsheet.
 
R

ryguy7272

Here is one way:
=CHOOSE(MATCH(A1,{"Jan";"Feb";"Mar";"Apr";"May"},1),"1","2","3","4","5")

Another way:
=LOOKUP(A17,{"Jan","Feb","Mar","Apr","May"},{"1","2","3","4","5"})


HTH,
Ryan---
 
J

Jacob Skaria

strMonth = "May"
intMonth = Month("01-" & strMonth & "-" & Year(Date))

If this post helps click Yes
 
S

salgud

Hmmmmm

1) Enter the values below in A1 to A12
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

2) In a general module, copy the following function:

Function test(tMth As Range)

test = Month(CVDate(tMth.Value & "-1-2000"))

End Function

3) Enter "=test" (without quotes) in B1.
4) copy down to B12

I get the numbers 1 to 12 in XL 2003.
Click to expand...

Thanks again. I ended up using Don's method. (see below)
 
A

AltaEgo

Hmmmmm

1) Enter the values below in A1 to A12
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

2) In a general module, copy the following function:

Function test(tMth As Range)

test = Month(CVDate(tMth.Value & "-1-2000"))

End Function

3) Enter "=test" (without quotes) in B1.
4) copy down to B12

I get the numbers 1 to 12 in XL 2003.
 
Top