How to convert text "JAN" into number 1?

E

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
 
G

Gary''s Student

Expand this formula:

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

for the remaining months
 
D

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.
 
B

Bernd P

Hello Eric,

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

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

Regards,
Bernd
 
L

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
 
J

Joe User

Lars-Åke Aspelin said:
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 -----
 
G

Gary''s Student

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


Lars-Ã…ke Aspelin said:
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
.
 
E

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
 
D

David Biddulph

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top