Changing to Proper Case

[Amanda17]

How can i change a whole database of capitals to proper case, i know the
=PROPER formula but i cant figure out how to work it for the whole
spreadsheet. Can someone PLEASE help me I really need this for work.

 

[carlo]

Hi Amanda

do you need to do it once, or lots of time?

If you need to do it once, make a new sheet, enter this formula to A1:
=proper(sheet1!A1)
you need to change sheet1 to whatever your sheetname is.
then, take the cell handler, and drag it down (may take a while)
After that with the whole column A selected, take the handler again
and drag it to the right to column IV.
Then press Ctrl+C, then right-click, paste special..., choose "values"

if you need to do it often, a sub might come in handy.
Something like:

Sub AllToProper()

For Each cell_ In ActiveSheet.UsedRange
If cell_.Value <> "" Then
cell_.Value =
Application.WorksheetFunction.Proper(cell_.Value)
End If

Next cell_

End Sub

hth

Carlo

 

[Gord Dibben]

To change the entire used range on a worksheet at once would require a Macro.

Here is one to change all cells to Proper Case.

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord

 

[Amanda17]

Thanks for your help, does this mean i need to copy each column (eg A, B, C,
D) individually into the new spreadshee. Its a huge database wit about 2,000
names and address and for mail merging purposes i require it in proper case.

 

[Amanda17]

Im not the greatest with Excel so all this seems a little confusing, where
do i put the formula so i can copy and paste it into the new worksheet

 

[carlo]

A step by step for the solution without VBA:
(At least you'll be able to work then, if you need more then that, you
could still ask later)

1. Add a new sheet to your workbook (i call it sheet1)
(You don't need to rename it yet!!!)
2. enter this formula into A1 on your new sheet:
=proper(YourSheet!A1)
(where YourSheet should be changed to the name of the relevant sheet)
3. Hit Enter
4. Select A1
5. Click and Hold the Handler
(lower right corner, the small dot on the border of your selection)
6. Drag the Handler down as far as you need it (around 2000 i assume)
7. Let the Handler go
8. Click again on the Handler and now Drag it to the right, as far as
you need it.
9. Press Ctrl + C
(Do not deselect after releasing the handler, otherwise you have to
reselect all your used cells)
10. Right click on A1
11. Choose "Paste Special..."
12. Check the "Values" option
13. Press Ok
14. Delete your sheet with the uppercase data
15. Rename sheet1 according to the old sheet.

hth

Carlo

 

[Amanda17]

Thankyou so much your a legand you really helped me out!!! I even rang excel
help and they told me that it wasnt possible to do it even though i knew it
was!!

 

[Gord Dibben]

Which formula is that?

Macros are not formulas.

Re-read the posting, check out the sites I posted if you still need help.


Gord

 

[Tyro]

What is a legand? Something to eat?

Thankyou so much your a legand you really helped me out!!! I even rang
excel
help and they told me that it wasnt possible to do it even though i knew
it
was!!