Combining formulas

Hi all,
I have 2 formulas:
=IF(LEFT(A1,1)="#",UPPER(MID(A1,1,2)),LOWER(MID(A1,1,2)))&LOWER(MID(A1,3,LEN(A1)))
and
=IF(LEFT(C1)="%",UPPER(C1),C1)

As it is right now, I have to format a column of data using the top formula
first, then reformat the data with the second formula once the first set is
copied to the C column. Is there any way to combine these formulas so I can
do them both in one step? Thanks!

Can there be anything else than # or % as the first character in A1?
Can you give an example of the string in A1?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"jezzica85" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
> I have 2 formulas:
> =IF(LEFT(A1,1)="#",UPPER(MID(A1,1,2)),LOWER(MID(A1,1,2)))&LOWER(MID(A1,3,LEN(A1)))
> and
> =IF(LEFT(C1)="%",UPPER(C1),C1)
>
> As it is right now, I have to format a column of data using the top
> formula
> first, then reformat the data with the second formula once the first set
> is
> copied to the C column. Is there any way to combine these formulas so I
> can
> do them both in one step? Thanks!

There can be a lot of things in A1:
example
Example
EXAMPLE
#Example
#Example's
#EXAMPLE
#EXAMPLE'S
%Example
%example
%EXAMPLE

I eventually want everything in front of the % to be uppercase like the last
example, and only the letter directly after the # to be capitalized.
Anything without either of those markers goes to lowercase. I know my two
formulas work fine to do that separately.


"Peo Sjoblom" wrote:

> Can there be anything else than # or % as the first character in A1?
> Can you give an example of the string in A1?
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "jezzica85" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all,
> > I have 2 formulas:
> > =IF(LEFT(A1,1)="#",UPPER(MID(A1,1,2)),LOWER(MID(A1,1,2)))&LOWER(MID(A1,3,LEN(A1)))
> > and
> > =IF(LEFT(C1)="%",UPPER(C1),C1)
> >
> > As it is right now, I have to format a column of data using the top
> > formula
> > first, then reformat the data with the second formula once the first set
> > is
> > copied to the C column. Is there any way to combine these formulas so I
> > can
> > do them both in one step? Thanks!
>
>

Assuming you had a typo here "in front of the % to be uppercase" you mean
after the %?
If so this should work


=IF(LEFT(A1)="#",UPPER(MID(A1,1,2))&LOWER(MID(A1,3,255)),IF(LEFT(A1)="%",UPPER(A1),LOWER(A1)))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"jezzica85" <[email protected]> wrote in message
news:[email protected]...
> There can be a lot of things in A1:
> example
> Example
> EXAMPLE
> #Example
> #Example's
> #EXAMPLE
> #EXAMPLE'S
> %Example
> %example
> %EXAMPLE
>
> I eventually want everything in front of the % to be uppercase like the
> last
> example, and only the letter directly after the # to be capitalized.
> Anything without either of those markers goes to lowercase. I know my two
> formulas work fine to do that separately.
>
>
> "Peo Sjoblom" wrote:
>
>> Can there be anything else than # or % as the first character in A1?
>> Can you give an example of the string in A1?
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> Northwest Excel Solutions
>>
>> www.nwexcelsolutions.com
>>
>> (remove ^^ from email address)
>>
>> Portland, Oregon
>>
>>
>>
>>
>> "jezzica85" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi all,
>> > I have 2 formulas:
>> > =IF(LEFT(A1,1)="#",UPPER(MID(A1,1,2)),LOWER(MID(A1,1,2)))&LOWER(MID(A1,3,LEN(A1)))
>> > and
>> > =IF(LEFT(C1)="%",UPPER(C1),C1)
>> >
>> > As it is right now, I have to format a column of data using the top
>> > formula
>> > first, then reformat the data with the second formula once the first
>> > set
>> > is
>> > copied to the C column. Is there any way to combine these formulas so
>> > I
>> > can
>> > do them both in one step? Thanks!
>>
>>

Thank you very much, that works. The other thing I've been trying to figure
out is if there's a way to delete the % or # in the cell after this
calculation is over. Is there a way to do that too? Thanks again, if you
don't know that's OK, you've already been a great help!

"Peo Sjoblom" wrote:

> Assuming you had a typo here "in front of the % to be uppercase" you mean
> after the %?
> If so this should work
>
>
> =IF(LEFT(A1)="#",UPPER(MID(A1,1,2))&LOWER(MID(A1,3,255)),IF(LEFT(A1)="%",UPPER(A1),LOWER(A1)))
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "jezzica85" <[email protected]> wrote in message
> news:[email protected]...
> > There can be a lot of things in A1:
> > example
> > Example
> > EXAMPLE
> > #Example
> > #Example's
> > #EXAMPLE
> > #EXAMPLE'S
> > %Example
> > %example
> > %EXAMPLE
> >
> > I eventually want everything in front of the % to be uppercase like the
> > last
> > example, and only the letter directly after the # to be capitalized.
> > Anything without either of those markers goes to lowercase. I know my two
> > formulas work fine to do that separately.
> >
> >
> > "Peo Sjoblom" wrote:
> >
> >> Can there be anything else than # or % as the first character in A1?
> >> Can you give an example of the string in A1?
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> Northwest Excel Solutions
> >>
> >> www.nwexcelsolutions.com
> >>
> >> (remove ^^ from email address)
> >>
> >> Portland, Oregon
> >>
> >>
> >>
> >>
> >> "jezzica85" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi all,
> >> > I have 2 formulas:
> >> > =IF(LEFT(A1,1)="#",UPPER(MID(A1,1,2)),LOWER(MID(A1,1,2)))&LOWER(MID(A1,3,LEN(A1)))
> >> > and
> >> > =IF(LEFT(C1)="%",UPPER(C1),C1)
> >> >
> >> > As it is right now, I have to format a column of data using the top
> >> > formula
> >> > first, then reformat the data with the second formula once the first
> >> > set
> >> > is
> >> > copied to the C column. Is there any way to combine these formulas so
> >> > I
> >> > can
> >> > do them both in one step? Thanks!
> >>
> >>
>
>

Never mind, I was proactive and actually did it! I think that formula you
put up got me on the right track. Thanks again so much!

"jezzica85" wrote:

> Hi all,
> I have 2 formulas:
> =IF(LEFT(A1,1)="#",UPPER(MID(A1,1,2)),LOWER(MID(A1,1,2)))&LOWER(MID(A1,3,LEN(A1)))
> and
> =IF(LEFT(C1)="%",UPPER(C1),C1)
>
> As it is right now, I have to format a column of data using the top formula
> first, then reformat the data with the second formula once the first set is
> copied to the C column. Is there any way to combine these formulas so I can
> do them both in one step? Thanks!