Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
=proper
I have a sheet with a bunch or columns. The values are in upper, lower and
combined case. Some are also numbers. I want to change all valuse to: first letter capital and the rest lowercase. I have messed around with the =proper function. The only way I can get this to work is if I put the function in a blank cell and then only do one cell at a time. Is there away to do the whole sheet at once and replace the current contents of each cell with the correct case? Thanks! Darren MCP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
=proper
Darren, you can do it with a macro, like this, select the range you want to
change and then run it Sub Proper_Case() 'select the range you want to change 'and run this macro Application.ScreenUpdating = False Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = Application.WorksheetFunction.Proper(Rng.Value) End If Next Rng Application.ScreenUpdating = True End Sub -- Paul B Always backup your data before trying something new Using Excel 97 & 2000 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Scrappy" wrote in message ... I have a sheet with a bunch or columns. The values are in upper, lower and combined case. Some are also numbers. I want to change all valuse to: first letter capital and the rest lowercase. I have messed around with the =proper function. The only way I can get this to work is if I put the function in a blank cell and then only do one cell at a time. Is there away to do the whole sheet at once and replace the current contents of each cell with the correct case? Thanks! Darren MCP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
=proper
Hi Darren,
You could do this with the following VBA subroutine: Sub MakeProperCase(rws As Worksheet) Dim rng As Range With rws.UsedRange For Each rng In .Cells rng.Formula = Application.WorksheetFunction. _ Proper(rng.Formula) Next rng End With End Sub Just call it like this: MakeProperCase Worksheets("Sheet1") '/ replace with name of your sheet This shouldn't mess anything up, but it's a good idea to save your Workbook before running it, as there's no undoing what VBA has wrought. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Scrappy wrote: I have a sheet with a bunch or columns. The values are in upper, lower and combined case. Some are also numbers. I want to change all valuse to: first letter capital and the rest lowercase. I have messed around with the =proper function. The only way I can get this to work is if I put the function in a blank cell and then only do one cell at a time. Is there away to do the whole sheet at once and replace the current contents of each cell with the correct case? Thanks! Darren MCP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
=proper
Thanks! They both worked great!
"Jake Marx" wrote in message ... Hi Darren, You could do this with the following VBA subroutine: Sub MakeProperCase(rws As Worksheet) Dim rng As Range With rws.UsedRange For Each rng In .Cells rng.Formula = Application.WorksheetFunction. _ Proper(rng.Formula) Next rng End With End Sub Just call it like this: MakeProperCase Worksheets("Sheet1") '/ replace with name of your sheet This shouldn't mess anything up, but it's a good idea to save your Workbook before running it, as there's no undoing what VBA has wrought. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Scrappy wrote: I have a sheet with a bunch or columns. The values are in upper, lower and combined case. Some are also numbers. I want to change all valuse to: first letter capital and the rest lowercase. I have messed around with the =proper function. The only way I can get this to work is if I put the function in a blank cell and then only do one cell at a time. Is there away to do the whole sheet at once and replace the current contents of each cell with the correct case? Thanks! Darren MCP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=PROPER() | Excel Discussion (Misc queries) | |||
Not Proper! | Excel Worksheet Functions | |||
proper | Excel Discussion (Misc queries) | |||
=PROPER | Excel Discussion (Misc queries) | |||
=PROPER | Excel Discussion (Misc queries) |