I need help desparately with PROPER formula...

[Guest]

Can someone please help me? I imported data from another program to Excel,
it is an address database. Some of the entries are all capital, some are all
lowercase, some are proper. I would like to switch everything to proper. I
have tried numberous times to do EXACTLY what I am reading about copy and
pasting, entering the formula, designating the cells I want it to apply to.
However, when I do it, nothing changes.

I am absolutely confused and frustrated. Can someone please help me resolve
this issue. Thank you!

-Lauren

 

[Bernard Liengme]

Let's experiment on a new workbook
Type some stuff in to A1:A5; like Bill Gates, LAUREN, Super Man, etc
In cell B1 enter =PROPER(A1) and copy it down to cell B5
Did that work?
Get back to us for more.

 

[Black Knight]

Can someone please help me? I imported data from another program to
Excel,
it is an address database. Some of the entries are all capital, some are
all
lowercase, some are proper. I would like to switch everything to proper.
I
have tried numberous times to do EXACTLY what I am reading about copy and
pasting, entering the formula, designating the cells I want it to apply
to.
However, when I do it, nothing changes.

I am absolutely confused and frustrated. Can someone please help me
resolve
this issue. Thank you!

Here's one way ..........

If your data is say in cells A1 to A100 then in cell B1 put =PROPER(A1)
and copy this down through all the cells to B100

Hey presto, all the cells in column B will display the 'proper' version of
column A

Then if you like you can copy all the cells in column B

'Paste Special' into column A (selecting the values option from the dialogue
box)

Then delete column B

Hope this helps

 

[Guest]

OK, what I am doing right now:

My worksheet has 925 rows and colums B-I.

In Colum J, I typed =PROPER(B2:B925) However, If I try to copy and paste
special, it doesnt apply to all of them. Do I really have to do it each
individual column and use the fill handle and stuff? There isnt an easier
way without using a macro? Something where I could type in =PROPER(B2:I925)
and it apply to all rows and colums?

If not, then this is still better than retyping everything.

Thanks for any help.

 

[Guest]

Thats what I did. What I meant was, can't I select ALL the cells I need it
to apply too, such as A2 through H925 and apply it to ALL the cells. Not
just one column at a time. Which this way I am doing A2 through A925, I type
in the formula in I2 (which is my first available BLANK cell) then I used the
fill handle to take that down to I925. Clicked Copy, then did paste special
over cells A2 through A925. Then I deleted column I's information then did
cells B2 through B925 by typing in column I "=PROPER(B2)" then fill handling
that down to I925 again and so on and so forth. So I am doing each column
individually. Is there an easier way besides doing each individual column??

 

[Pete]

Lauren,

what you could have done is to highlight all the cells you want the
formula to apply to first (i.e. J2 to Q925). Then enter the formula

=PROPER(B2)

but do CTRL-Enter rather than just <Enter> and this will fill the
formula for you automatically.

Pete

 

[Pete]

Sorry, to complete what you asked for ...

The area where the formulae have been copied will still be highlighted,
so click <copy> then Edit | Paste Special | Values | OK and <esc> or
<enter>. This will fix the values in place, then highlight columns B to
I by clicking on the column identifiers and Edit | Delete. I think you
will find this to be quicker than how you did it before.

Incidentally, you could have copied the formula from J2 to K2:Q2, then
highlighted J2:Q2 and double-clicked the fill handle - this would also
have been quicker than the way you described.

Pete

 

[Bernard Liengme]

No, I wrote =PROPER(A1)
and drag the fill handle down the column

This is a fundamental skill in Excel - typing a single formula and them
making it apply to various rows in a column

 

[Black Knight]

Thats what I did. What I meant was, can't I select ALL the cells I need
it
to apply too, such as A2 through H925 and apply it to ALL the cells. Not
just one column at a time. Which this way I am doing A2 through A925, I
type
in the formula in I2 (which is my first available BLANK cell) then I used
the
fill handle to take that down to I925. Clicked Copy, then did paste
special
over cells A2 through A925. Then I deleted column I's information then
did
cells B2 through B925 by typing in column I "=PROPER(B2)" then fill
handling
that down to I925 again and so on and so forth. So I am doing each column
individually. Is there an easier way besides doing each individual
column??

If you're using Sheet1 in your workbook you could put the formula in the
corresponding cells in Sheet2

Like this

=PROPER(Sheet1!A1)

Copy this to all the corresponding cells in Sheet2 and it will display the
'Proper' formats of the cells from Sheet1

Then you could copy and paste special all the cells from Sheet2 back to
Sheet1

 

[David McRitchie]

Hi Lauren,
I expect you are straight on use of the worksheet formula now and use
of the fill handle. If not more on fill handle see
http://www.mvps.org/dmcritchie/excel/fillhand.htm
if this is an adjacent cell you can probably double-click on the fill handle,
but it usually means verifying that you did reach down as far as you wanted.
In any case as Bernard stated it is basic Excel skills so you should learn it
before learning to use macros (even if by only a few minutes).

But it sounds like you want to select the area and make the changes in place,
that is where a macro comes in, macro is a lot quicker See
http://www.mvps.org/dmcritchie/excel/proper.htm