From: Steve on 11 Mar 2010 16:44 Very nice. And clean too. Thanks much, Steve "Fred Smith" wrote: > Use Substitute, as in: > =Substitute(P21,"/","") > > Regards, > Fred > > "Steve" wrote in message > news:D1729A01-069B-4F49-BE64-678E1C6E3788(a)microsoft.com... > >I have days off in the P column in this format: Sat/Sun > > I'm using them in another column in this format SatSun, so I'm using this > > formula: > > =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun > > However, I have some days off that just have Sun, which produces SunSun. > > How can the formula be writen to produce SatSun when two days are showing, > > and only Sun when one day is showing ? > > > > Thanks, > > > > Steve > > . > From: Fred Smith on 11 Mar 2010 18:41 You're welcome. Thanks for the feedback. Fred. "Steve" wrote in message news:9B56F454-93C8-4610-97BB-28AE0B42C503(a)microsoft.com...> Very nice. And clean too. > > Thanks much, > > Steve > > "Fred Smith" wrote: > >> Use Substitute, as in: >> =Substitute(P21,"/","") >> >> Regards, >> Fred >> >> "Steve" wrote in message >> news:D1729A01-069B-4F49-BE64-678E1C6E3788(a)microsoft.com... >> >I have days off in the P column in this format: Sat/Sun >> > I'm using them in another column in this format SatSun, so I'm using >> > this >> > formula: >> > =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun >> > However, I have some days off that just have Sun, which produces >> > SunSun. >> > How can the formula be writen to produce SatSun when two days are >> > showing, >> > and only Sun when one day is showing ? >> > >> > Thanks, >> > >> > Steve >> >> . >> From: ker_01 on 11 Mar 2010 19:21 =if(len(P21)>3,LEFT(P21,3)&RIGHT(P21,3),If(len(P21)>0,P21,"")) aircode, so double check the paran placement, but this takes care of the "empty cells equal zero" problem. "Steve" wrote: > Thanks, > > Works great, but I'm getting zeros now in the cells when there is nothing in > the P column cells, which is a lot. How can I eleminate the zeros if nothing > is in the P column cell ? > > "ker_01" wrote: > > > =if(len(P21)>3,LEFT(P21,3)&RIGHT(P21,3),P21) > > > > Assumes that cell P21 has nothing in it but either Sat, Sun, or Sat...Sun > > > > HTH, > > Keith > > > > "Steve" wrote: > > > > > I have days off in the P column in this format: Sat/Sun > > > I'm using them in another column in this format SatSun, so I'm using this > > > formula: > > > =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun > > > However, I have some days off that just have Sun, which produces SunSun. > > > How can the formula be writen to produce SatSun when two days are showing, > > > and only Sun when one day is showing ? > > > > > > Thanks, > > > > > > Steve From: Steve on 15 Mar 2010 12:30 That'll do it. Thank you, Steve "ker_01" wrote: > > =if(len(P21)>3,LEFT(P21,3)&RIGHT(P21,3),If(len(P21)>0,P21,"")) > aircode, so double check the paran placement, but this takes care of the > "empty cells equal zero" problem. > > "Steve" wrote: > > > Thanks, > > > > Works great, but I'm getting zeros now in the cells when there is nothing in > > the P column cells, which is a lot. How can I eleminate the zeros if nothing > > is in the P column cell ? > > > > "ker_01" wrote: > > > > > =if(len(P21)>3,LEFT(P21,3)&RIGHT(P21,3),P21) > > > > > > Assumes that cell P21 has nothing in it but either Sat, Sun, or Sat...Sun > > > > > > HTH, > > > Keith > > > > > > "Steve" wrote: > > > > > > > I have days off in the P column in this format: Sat/Sun > > > > I'm using them in another column in this format SatSun, so I'm using this > > > > formula: > > > > =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun > > > > However, I have some days off that just have Sun, which produces SunSun. > > > > How can the formula be writen to produce SatSun when two days are showing, > > > > and only Sun when one day is showing ? > > > > > > > > Thanks, > > > > > > > > Steve First  |  Prev  |