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!

Combining formulas
 

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" wrote in message
...
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!

Combining formulas
 

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" wrote in message
...
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!

Combining formulas
 

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" wrote in message
...
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" wrote in message
...
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!

Combining formulas
 

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" wrote in message
...
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" wrote in message
...
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!

Combining formulas
 

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!