How to convert text "JAN" into number 1?

  • Thread starter Thread starter Eric
  • Start date Start date
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
 
If your abbreviations are nice, you could use a formula like:

=month("1"&A1)

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

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

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

Regards,
Bernd
 
Expand this formula:

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

for the remaining months
Click to expand...

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
 
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})
Click to expand...

Easier and less error-prone:

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


----- original message -----
 
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
.
Click to expand...
 
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
 
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.
 
Back
Top