How to convert text "JAN" into number 1?

[Eric]

Does anyone have any suggestions on how to convert text "JAN" into number 1?
Does excel have any built in function to do it?
For all the months' symbol into numbers
"JAN" into 1
...
"MAR" into 3
...
"DEC" into 12

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Gary''s Student]

Expand this formula:

=LOOKUP(A1,{"JAN","FEB"},{1,2})

for the remaining months

 

[Dave Peterson]

If your abbreviations are nice, you could use a formula like:

=month("1"&A1)

If your abbreviations aren't nice, it won't work.

 

[Bernd P]

Hello Eric,

In UK you can use
=MONTH("1/"&A1)

In USA it is probably
=MONTH(A1&"/1")

Regards,
Bernd

 

[Lars-Åke Aspelin]

Expand this formula:

=LOOKUP(A1,{"JAN","FEB"},{1,2})

for the remaining months

The values in the lookup vector have to be in ascending order, so you
need to sort the months like this to have the formula working:

=LOOKUP(A1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN","MAR","MAY","NOV","OCT","SEP"},{4,8,12,2,1,7,6,3,5,11,10,9})

Hope this helps / Lars-Åke

 

[Joe User]

The values in the lookup vector have to be in ascending
order, so you need to sort the months like this to have
the formula working:
=LOOKUP(A1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN"
,"MAR","MAY","NOV","OCT","SEP"},{4,8,12,2,1,7,6,3,5,11,10,9})

Easier and less error-prone:

=MATCH(A1,{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0)


----- original message -----

 

[Gary''s Student]

Thank you!
--
Gary''s Student - gsnu201001

The values in the lookup vector have to be in ascending order, so you
need to sort the months like this to have the formula working:

=LOOKUP(A1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN","MAR","MAY","NOV","OCT","SEP"},{4,8,12,2,1,7,6,3,5,11,10,9})

Hope this helps / Lars-Ã…ke
.

 

[Eric]

Wow! magic, I don't know why it works this way, but it works beautifully
Do you have any suggestions on how it works this way?
Thank everyone very much for suggestions
Eric

 

[David Biddulph]

Easy. The MONTH function gives the month number from a date. 1Jan is
interpreted as a date.

 

[Teethless mama]

=MONTH(A1&1)

 

[Dave Peterson]

Just to add to David's response...

I'm amazed at how many different string formats excel can see as a date. It
comes in particularly handy when doing data|text to columns.