How do I change the case from all caps to proper for names I have in columns E, F, G?

[duugg]

I know that a formula would work, but I don't know what it is
and more importantly, where to put it.

In other words, if the "all caps name" is in E3 to E120

and I want to change these cells to "proper case"

Note that I already tried putting this formula in A1
=PROPER(A2)

to change an "all caps" name in A2, but it didn't change it.

What exactly do I do for this?

If you will be providing me with a formula, then please tell me exactly
where to put it and how to put it there.

Thanks so much

 

[dlw]

You can't change the existing cells, in blank cells, put in =proper(E3) and
the proper name will show up in the cell you put the formula in. Then you
have to do a copy / paste special /values to turn the formula into the
actual names.

 

[Zack Barresse]

Hi duugg,

What results is the formula returning exactly? That should be the formula
you need...

 

[duugg]

dlw

If I put the formula into cell , I get the following error message
=PROPER(#REF!). Then I put the name, let's say, John Smith in the same
cell, but it types over the formula, resulting in erasing it.

I also tried putting "=Proper (A2)" into cell A1 and then typing john
smith (just like that, all lowercase) in cell A2, but it does not
change the case to proper after pressing enter, it just stays like it
was.

 

[Zack Barresse]

The #REF! is generally from deleting or cutting the referenced cell (if
you're seeing it in the formula itself). When you change the #REF! to A2
and change A2 value to "john doe" (w/o quotes), what does the formula read?
If the same, press F9, what does the formula read? If the same, select your
formula, press F2 then Enter, what does it read now?

 

[duugg]

Zack,

We'll it works if I type the name, erase it, and then re-type it. I
want to use this formula on 120 pre-existing cells. If I have to erase
it, and then retype it, it will defeat the purpose.

Is there a way to use this on pre-existing data. Obviously, even if I
have to copy and paste a column into a blank worksheet to do this, it
would be okay.

If so, do I insert a row above all others to put the formula in just to
correct the text entries below that I want to change the case on?

 

[duugg]

Zack,

We'll it works if I type the name, erase it, and then re-type it. I
want to use this formula on 120 pre-existing cells. If I have to erase
it, and then retype it, it will defeat the purpose.

Is there a way to use this on pre-existing data. Obviously, even if I
have to copy and paste a column into a blank worksheet to do this, it
would be okay.

If so, do I insert a row above all others to put the formula in just to
correct the text entries below that I want to change the case on?

 

[duugg]

Zack,

We'll it works if I type the name, erase it, and then re-type it.
want to use this formula on 120 pre-existing cells. If I have to eras
it, and then retype it, it will defeat the purpose.

Is there a way to use this on pre-existing data. Obviously, even if
have to copy and paste a column into a blank worksheet to do this, i
would be okay.

If so, do I insert a row above all others to put the formula in just t
correct the text entries below that I want to change the case on

 

[Gord Dibben]

duugg

Assume the names are in in cells E1:E120

Also assume your calculation mode is set to automatic under
Tools>Options>Calculation.

In F1 enter =PROPER(E1)

Hover the mouse pointer over the bottom right corner of F1 untill you see a
black cross. Double-click on that cross to copy the formula down to F120

I believe your mistake is you are trying to enter a formula and the name in the
same cell.

Cannot be done.


Gord Dibben MS Excel MVP

 

[duugg]

Ugh! This is driving me crazy! Why is it not changing the names to
proper??

Sorry everyone, I feel oblivious to this. I've attached a pic of the
spreadsheet to show everyone that it's not changing the text to
proper.

I do have it set to automatic as well.

Again, thanks for everyone's help on this.


+-------------------------------------------------------------------+
|Filename: untitled 4.bmp |
|Download: http://www.excelforum.com/attachment.php?postid=4689 |
+-------------------------------------------------------------------+

 

[duugg]

I'm really bummin on this one.

 

[Gary Brown]

The format of cells F1 to F3 is probably set to Text, change this to
General, then on each of the cells, press F2 to edit, then press
Return.

 

[Gary Brown]

The format of cells F1 to F3 is probably set to Text, change this to
General, then on each of the cells, press F2 to edit, then press
Return.

 

[duugg]

Gary, unfortunately that whole column is set to general, good thinking
though, I hadn't checked that.

Any other ideas?

 

[Zack Barresse]

You mean the target cell? After you type it the first time, select the
formula cell. What exactly does it say? Now press F2 and then Enter. Now
what does it say exactly?

 

[duugg]

Zack,

Okay, here is exactly what I'm doing

Cell F1 contains this =PROPER(E1)
Cell E1 (currently blank) I type "john smith" (without the quotes)
I press enter and nothing happens, nothing appears on the screen, case
stays the same (lower, not proper).

Thanks for sticking with this.

duugg

 

[Gord Dibben]

F1 should read John Smith

E1 will read the "john smith" you typed into it and will not change.

What do you see in F1 after you type john smith in E1 and hit F9 in case you
have calculation at manual?


Gord

 

[Zack Barresse]

After typing "john smith" (w/o quotes) in E1 and pressing enter, what
happens with each of the following:

(Try first)
Hitting F9

Selecting F1, press F2, Enter

 

[duugg]

Zack,

Nothing, nothing and nothing, no joke

 

[vcard]

Any possibility that text format colour is set up as white, so that it's not
visible??

vcard

Zack,

Nothing, nothing and nothing, no joke.


--

*** ***