Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Tom
 
Posts: n/a
Default 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   Report Post  
Don Guillett
 
Posts: n/a
Default

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   Report Post  
Tom
 
Posts: n/a
Default

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   Report Post  
Tom
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I delete blank rows at the bottom of a spreadsheet to get . Miklaurie Excel Discussion (Misc queries) 1 January 26th 05 03:30 PM
copy qualifying rows to another spreadsheet acpharmd Excel Worksheet Functions 1 December 29th 04 10:45 PM
How do I change a spreadsheet from all caps to "Proper" dumbfounded Excel Discussion (Misc queries) 9 November 30th 04 09:22 PM
Data Filter - Not all rows in spreadsheet will display in Autofilt Excel Help Excel Worksheet Functions 1 November 17th 04 06:40 PM
How can I sort an entire spreadsheet from a list prod sorter Excel Worksheet Functions 4 November 17th 04 04:43 AM


All times are GMT +1. The time now is 12:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"