Replacing capital letters
 

Hi all,
Does anyone know if it's possible to take a big range of data and replace
all capital letters with lowercase ones, except if they're directly in front
of a symbol? As an example,

Example would turn into example
EXAMPLE would turn into example
#Example would stay #Example
#EXAMPLE would turn into #Example

I'm macro challenged, so I have no idea how to write or use macros. If
there's a formula for this, please tell me, but if there's a macro can you
please tell me what it is and how to set it up so it works?

Thank you so much!

Replacing capital letters
 

Try

=IF(LEFT(A1)="#","#"&PROPER(RIGHT(A1,LEN(A1)-1)),LOWER(A1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jezzica85" wrote in
message
...
Hi all,
Does anyone know if it's possible to take a big range of data
and replace
all capital letters with lowercase ones, except if they're
directly in front
of a symbol? As an example,

Example would turn into example
EXAMPLE would turn into example
#Example would stay #Example
#EXAMPLE would turn into #Example

I'm macro challenged, so I have no idea how to write or use
macros. If
there's a formula for this, please tell me, but if there's a
macro can you
please tell me what it is and how to set it up so it works?

Thank you so much!

Replacing capital letters
 

This is going to sound silly, but will that work for a whole sheet? And
where would I put it?

"Chip Pearson" wrote:

Try

=IF(LEFT(A1)="#","#"&PROPER(RIGHT(A1,LEN(A1)-1)),LOWER(A1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jezzica85" wrote in
message
...
Hi all,
Does anyone know if it's possible to take a big range of data
and replace
all capital letters with lowercase ones, except if they're
directly in front
of a symbol? As an example,

Example would turn into example
EXAMPLE would turn into example
#Example would stay #Example
#EXAMPLE would turn into #Example

I'm macro challenged, so I have no idea how to write or use
macros. If
there's a formula for this, please tell me, but if there's a
macro can you
please tell me what it is and how to set it up so it works?

Thank you so much!

Replacing capital letters
 

Never mind, I figured that out, but I noticed this formula messes up on
apostrophes and acronyms, so I get things like:

Example'S
and
Teb (Short for "this example is really bad :) )
I'Ve

when I should get:
Example's
TEB
I've

I know I can go through them manually, but just for trivia sake, is there a
way to modify the formula just a little so it can handle apostrophes and
acronyms? Acronyms aren't possible, probably, but apostrophes maybe?

"jezzica85" wrote:

This is going to sound silly, but will that work for a whole sheet? And
where would I put it?

"Chip Pearson" wrote:

Try

=IF(LEFT(A1)="#","#"&PROPER(RIGHT(A1,LEN(A1)-1)),LOWER(A1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jezzica85" wrote in
message
...
Hi all,
Does anyone know if it's possible to take a big range of data
and replace
all capital letters with lowercase ones, except if they're
directly in front
of a symbol? As an example,

Example would turn into example
EXAMPLE would turn into example
#Example would stay #Example
#EXAMPLE would turn into #Example

I'm macro challenged, so I have no idea how to write or use
macros. If
there's a formula for this, please tell me, but if there's a
macro can you
please tell me what it is and how to set it up so it works?

Thank you so much!

Replacing capital letters
 

How about:

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

(all one cell)

If it starts with #, then capitalize the first two characters.
Else make the first two characters lower case.
(since the first character is #, it won't change.)

Then always use lower case for the last xx characters (starting in position 3)

But
Example's becomes example's (since it didn't start with #)
and
TEB becomes teb

#ExAmPlE's becomes #Example's though.



jezzica85 wrote:

Never mind, I figured that out, but I noticed this formula messes up on
apostrophes and acronyms, so I get things like:

Example'S
and
Teb (Short for "this example is really bad :) )
I'Ve

when I should get:
Example's
TEB
I've

I know I can go through them manually, but just for trivia sake, is there a
way to modify the formula just a little so it can handle apostrophes and
acronyms? Acronyms aren't possible, probably, but apostrophes maybe?

"jezzica85" wrote:

This is going to sound silly, but will that work for a whole sheet? And
where would I put it?

"Chip Pearson" wrote:

Try

=IF(LEFT(A1)="#","#"&PROPER(RIGHT(A1,LEN(A1)-1)),LOWER(A1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jezzica85" wrote in
message
...
Hi all,
Does anyone know if it's possible to take a big range of data
and replace
all capital letters with lowercase ones, except if they're
directly in front
of a symbol? As an example,

Example would turn into example
EXAMPLE would turn into example
#Example would stay #Example
#EXAMPLE would turn into #Example

I'm macro challenged, so I have no idea how to write or use
macros. If
there's a formula for this, please tell me, but if there's a
macro can you
please tell me what it is and how to set it up so it works?

Thank you so much!




--

Dave Peterson

Replacing lower case
 

Chip:

You gave a formula for replacing upper case characters with lower case, but
I have a different problem:

I need to replace the first character in columns 2, 3, and 5 with an upper
case letter (I often miss doing them manually). Do you know of a formula for
that??

NancyR

"Chip Pearson" wrote:

Try

=IF(LEFT(A1)="#","#"&PROPER(RIGHT(A1,LEN(A1)-1)),LOWER(A1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jezzica85" wrote in
message
...
Hi all,
Does anyone know if it's possible to take a big range of data
and replace
all capital letters with lowercase ones, except if they're
directly in front
of a symbol? As an example,

Example would turn into example
EXAMPLE would turn into example
#Example would stay #Example
#EXAMPLE would turn into #Example

I'm macro challenged, so I have no idea how to write or use
macros. If
there's a formula for this, please tell me, but if there's a
macro can you
please tell me what it is and how to set it up so it works?

Thank you so much!