VBA Function which is somewhat a Reverse of CHOOSE

[x0nar]

We all know how CHOOSE Works

eg. If I had month number and I wanted to convert them into Month name then I'd be using Choose
ie.

MonName=CHOOSE(Monno,"Jan", "Feb", "Mar", "Apr", "May" "Jun", "Jul", "Aug", "Sep", "Oct" "Nov" "Dec")

But what my query is that if we were given month names and were asked to find their respective numbers how would one do that.

I am aware of the use of

InStr

to find the relative number of a character from a string.

What I wanted to ask is if it was possible to consider a word instead of a single character to find out their relative number from a string.

Thanks.

 

[FormR]

Hi, if your string that contains your month name is, for example:

Dim sMonth As String
sMonth = "May"

Then for your specific scenario you could use something like this to get the month number:

MsgBox Month(sMonth & "-1")

Or more generally you could use Excels Match() function:

MsgBox Application.Match(sMonth, Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)

And FWIW, you can also get the month name by using the MonthNane function:

Dim Mono As Long
Mono = 5
MsgBox MonthName(Mono)

 

[azumi]

You can use simple formula :

=MONTH(A1&1)

or

=MONTH(DATEVALUE(A1&" 1"))

or

=MONTH(DATEVALUE("01-"&A1))

or

=MATCH(A1,{"January","February","March","April","May","June","July","August","September","October","November","December"},0)

 

[x0nar]

Hi, if your string that contains your month name is, for example:

Dim sMonth As String
sMonth = "May"

Then for your specific scenario you could use something like this to get the month number:

MsgBox Month(sMonth & "-1")

Or more generally you could use Excels Match() function:

MsgBox Application.Match(sMonth, Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)

And FWIW, you can also get the month name by using the MonthNane function:

Dim Mono As Long
Mono = 5
MsgBox MonthName(Mono)

Thanks MATCH was exactly what I was looking for.