How to convert text "JAN" into number 1?
in [General]
|
Prev: Autofilter header row
Next: IF nested problem ..!
From: Joe User on 10 Apr 2010 11:17 "Lars-�ke Aspelin" <larske(a)REMOOVEtelia.com> wrote: > 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 ----- "Lars-�ke Aspelin" <larske(a)REMOOVEtelia.com> wrote in message news:9d31s5d16qk13gqsddmejfj2n4riprrbla(a)4ax.com... > 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
From: Gary''s Student on 10 Apr 2010 11:34 Thank you! -- Gary''s Student - gsnu201001 "Lars-Åke Aspelin" wrote: > 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 > . >
From: Eric on 10 Apr 2010 11:35 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 "Dave Peterson" wrote: > 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: "David Biddulph" groups [at] on 10 Apr 2010 12:29 Easy. The MONTH function gives the month number from a date. 1Jan is interpreted as a date. -- David Biddulph "Eric" <Eric(a)discussions.microsoft.com> wrote in message news:A19C7129-AD74-406E-825A-50240237D371(a)microsoft.com... > 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 > > "Dave Peterson" wrote: > >> 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: Teethless mama on 10 Apr 2010 13:48
=MONTH(A1&1) "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 |