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!

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" <[email protected]> wrote in
message
news:[email protected]...
> 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!

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" <[email protected]> wrote in
> message
> news:[email protected]...
> > 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!
>
>
>

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" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > > 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!
> >
> >
> >

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" <[email protected]> wrote in
> > > message
> > > news:[email protected]...
> > > > 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