How to convert text "JAN" into number 1?
in [General]
|
Prev: Autofilter header row
Next: IF nested problem ..!
From: Eric on 10 Apr 2010 10:13 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
From: Gary''s Student on 10 Apr 2010 10:26 Expand this formula: =LOOKUP(A1,{"JAN","FEB"},{1,2}) for the remaining months -- Gary''s Student - gsnu201001 "Eric" wrote: > 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
From: Dave Peterson on 10 Apr 2010 10:38 If your abbreviations are nice, you could use a formula like: =month("1"&A1) If your abbreviations aren't nice, it won't work. Eric wrote: > > 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 -- Dave Peterson
From: Bernd P on 10 Apr 2010 10:38 Hello Eric, In UK you can use =MONTH("1/"&A1) In USA it is probably =MONTH(A1&"/1") Regards, Bernd
From: Lars-�ke Aspelin on 10 Apr 2010 10:40
On Sat, 10 Apr 2010 07:26:02 -0700, Gary''s Student <GarysStudent(a)discussions.microsoft.com> wrote: >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 |