Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using PROPER for Columns, rows or ENTIRE spreadsheet
The function PROPER() works well for changing single cells to the proper
case. What I need is to convert the entire spreadsheet, or specific columns or rows, to the proper case. That is, if column C has some or all names in a variety of cases, I'd like to change that to column C with all proper. Sort of a PROPER(Column C). Same for row 12 to be PROPER(Row 12). I'm getting data that is mainly uppercase which includes the state ID, such as TX for Texas, etc. I don't want this to be made proper. But the names, cities, addresses, and some descriptions in the data, I'd like to convert to proper format. Does there exist a utility or a way to structure the PROPER function so I don't have to do each cell? TIA, Tom |
#2
|
|||
|
|||
This might prove useful. I have it in my personal.xls
Sub ChangeCase() Application.ScreenUpdating = False Dim r As Range nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select Case Desired")) Select Case nCase Case "L" For Each r In Selection.Cells If r.HasFormula Then r.Formula = LCase(r.Formula) 'R.Formula = R.Value Else r.Value = LCase(r.Value) End If Next Case "U" For Each r In Selection.Cells If r.HasFormula Then r.Formula = UCase(r.Formula) 'R.Formula = R.Value Else r.Value = UCase(r.Value) End If Next Case "P" For Each r In Selection.Cells If r.HasFormula Then r.Formula = Application.Proper(r.Formula) 'R.Formula = R.Value Else r.Value = StrConv(r.Value, vbProperCase) End If Next End Select Application.ScreenUpdating = True End Sub -- Don Guillett SalesAid Software "Tom" wrote in message ... The function PROPER() works well for changing single cells to the proper case. What I need is to convert the entire spreadsheet, or specific columns or rows, to the proper case. That is, if column C has some or all names in a variety of cases, I'd like to change that to column C with all proper. Sort of a PROPER(Column C). Same for row 12 to be PROPER(Row 12). I'm getting data that is mainly uppercase which includes the state ID, such as TX for Texas, etc. I don't want this to be made proper. But the names, cities, addresses, and some descriptions in the data, I'd like to convert to proper format. Does there exist a utility or a way to structure the PROPER function so I don't have to do each cell? TIA, Tom |
#3
|
|||
|
|||
Thanks Don,
Since I've not put macros into Excel before, where does this go and how do I invoke it? Thanks again!!! Tom "Don Guillett" wrote: This might prove useful. I have it in my personal.xls Sub ChangeCase() Application.ScreenUpdating = False Dim r As Range nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select Case Desired")) Select Case nCase Case "L" For Each r In Selection.Cells If r.HasFormula Then r.Formula = LCase(r.Formula) 'R.Formula = R.Value Else r.Value = LCase(r.Value) End If Next Case "U" For Each r In Selection.Cells If r.HasFormula Then r.Formula = UCase(r.Formula) 'R.Formula = R.Value Else r.Value = UCase(r.Value) End If Next Case "P" For Each r In Selection.Cells If r.HasFormula Then r.Formula = Application.Proper(r.Formula) 'R.Formula = R.Value Else r.Value = StrConv(r.Value, vbProperCase) End If Next End Select Application.ScreenUpdating = True End Sub -- Don Guillett SalesAid Software "Tom" wrote in message ... The function PROPER() works well for changing single cells to the proper case. What I need is to convert the entire spreadsheet, or specific columns or rows, to the proper case. That is, if column C has some or all names in a variety of cases, I'd like to change that to column C with all proper. Sort of a PROPER(Column C). Same for row 12 to be PROPER(Row 12). I'm getting data that is mainly uppercase which includes the state ID, such as TX for Texas, etc. I don't want this to be made proper. But the names, cities, addresses, and some descriptions in the data, I'd like to convert to proper format. Does there exist a utility or a way to structure the PROPER function so I don't have to do each cell? TIA, Tom |
#4
|
|||
|
|||
Thanks, Don!!!
This works EXACTLY what I needed..... Thanks Tom "Tom" wrote: Thanks Don, Since I've not put macros into Excel before, where does this go and how do I invoke it? Thanks again!!! Tom "Don Guillett" wrote: This might prove useful. I have it in my personal.xls Sub ChangeCase() Application.ScreenUpdating = False Dim r As Range nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select Case Desired")) Select Case nCase Case "L" For Each r In Selection.Cells If r.HasFormula Then r.Formula = LCase(r.Formula) 'R.Formula = R.Value Else r.Value = LCase(r.Value) End If Next Case "U" For Each r In Selection.Cells If r.HasFormula Then r.Formula = UCase(r.Formula) 'R.Formula = R.Value Else r.Value = UCase(r.Value) End If Next Case "P" For Each r In Selection.Cells If r.HasFormula Then r.Formula = Application.Proper(r.Formula) 'R.Formula = R.Value Else r.Value = StrConv(r.Value, vbProperCase) End If Next End Select Application.ScreenUpdating = True End Sub -- Don Guillett SalesAid Software "Tom" wrote in message ... The function PROPER() works well for changing single cells to the proper case. What I need is to convert the entire spreadsheet, or specific columns or rows, to the proper case. That is, if column C has some or all names in a variety of cases, I'd like to change that to column C with all proper. Sort of a PROPER(Column C). Same for row 12 to be PROPER(Row 12). I'm getting data that is mainly uppercase which includes the state ID, such as TX for Texas, etc. I don't want this to be made proper. But the names, cities, addresses, and some descriptions in the data, I'd like to convert to proper format. Does there exist a utility or a way to structure the PROPER function so I don't have to do each cell? TIA, Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete blank rows at the bottom of a spreadsheet to get . | Excel Discussion (Misc queries) | |||
copy qualifying rows to another spreadsheet | Excel Worksheet Functions | |||
How do I change a spreadsheet from all caps to "Proper" | Excel Discussion (Misc queries) | |||
Data Filter - Not all rows in spreadsheet will display in Autofilt | Excel Worksheet Functions | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |