Thread: left/right or center ?

Use Substitute, as in:
=Substitute(P21,"/","")

Regards,
Fred

"Steve" <Steve@discussions.microsoft.com> wrote in message
news1729A01-069B-4F49-BE64-678E1C6E3788@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

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

=IF(LEN(P21)=7,LEFT(P21,3)&RIGHT(P21,3),P21)

Yes, much better!

Fred Smith wrote:
> Use Substitute, as in:
> =Substitute(P21,"/","")
>
> Regards,
> Fred
>
> "Steve" <Steve@discussions.microsoft.com> wrote in message
> news1729A01-069B-4F49-BE64-678E1C6E3788@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
>

=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

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

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 ?


"Glenn" wrote:

> 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
>
> =IF(LEN(P21)=7,LEFT(P21,3)&RIGHT(P21,3),P21)
> .
>

I don't understand where to put this in my regular formula.
Thanks,

Steve

"Fred Smith" wrote:

> Use Substitute, as in:
> =Substitute(P21,"/","")
>
> Regards,
> Fred
>
> "Steve" <Steve@discussions.microsoft.com> wrote in message
> news1729A01-069B-4F49-BE64-678E1C6E3788@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
>
> .
>

It doesn't go in your regular formula, it replaces it.

Steve wrote:
> I don't understand where to put this in my regular formula.
> Thanks,
>
> Steve
>
> "Fred Smith" wrote:
>
>> Use Substitute, as in:
>> =Substitute(P21,"/","")
>>
>> Regards,
>> Fred
>>
>> "Steve" <Steve@discussions.microsoft.com> wrote in message
>> news1729A01-069B-4F49-BE64-678E1C6E3788@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
>> .
>>