Replacing capital letters

G

Guest

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!
 
G

Guest

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

Guest

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?
 
D

Dave Peterson

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.
 
N

NancyR

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

capital letters to lowercase 5
Change the color of each capital letter in a rich text memo field. 0
How do I display a number in writing (letters) 1
Find and Replace variable string 11
Capital Letters 2
format number custom want some values in red 6
Cannot type SOME double letters in MS Word 2
create a bullited list with capital letters inside of circles 2

Top