Changing cases to proper
 

I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

Insert a helper column. Enter the formula =proper(a1), and copy down. Copy the
new column, and Paste SpecialValues the old column. Delete the helper column.
Repeat for each column.

You will probably have to manually correct some names, especially last names
beginning with Mc and Mac.

--
Regards,
Fred


"dennis blaz" wrote in message
...
I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

I inserted a new column (a), I highlight the column and I enter =proper (a1)
and the a1 cell comes up w/ an "o", what am I doing wrong here?

"Fred Smith" wrote:

Insert a helper column. Enter the formula =proper(a1), and copy down. Copy the
new column, and Paste SpecialValues the old column. Delete the helper column.
Repeat for each column.

You will probably have to manually correct some names, especially last names
beginning with Mc and Mac.

--
Regards,
Fred


"dennis blaz" wrote in message
...
I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and Mc's and
other stuff.


Gord Dibben MS Excel MVP

On Wed, 1 Aug 2007 20:44:01 -0700, dennis blaz
wrote:

I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

Thanks Gord, but that's way too complicated for me. I think I'd rather just
manually change the few complex names and use an easier method.

"Gord Dibben" wrote:

To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and Mc's and
other stuff.


Gord Dibben MS Excel MVP

On Wed, 1 Aug 2007 20:44:01 -0700, dennis blaz
wrote:

I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

On 2 Aug, 05:26, dennis blaz
wrote:
Thanks Gord, but that's way too complicated for me. I think I'd rather just
manually change the few complex names and use an easier method.



"Gord Dibben" wrote:
To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and Mc's and
other stuff.

Gord Dibben MS Excel MVP

On Wed, 1 Aug 2007 20:44:01 -0700, dennis blaz
wrote:

I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns- Hide quoted text -

- Show quoted text -

Actually, the VBA for this is VERY easy

Sub changer()
Dim cell
For Each cell In ActiveSheet.UsedRange
cell.Value = WorksheetFunction.Proper(cell.Value)
Next
End Sub

Changing cases to proper
 

Hi Dennis,
Your referencing the first cell in your helper column change the formula to
=PROPER(B1) where B1 is the first cell of your names.

Hope this helps,

Gav.

"dennis blaz" wrote:

I inserted a new column (a), I highlight the column and I enter =proper (a1)
and the a1 cell comes up w/ an "o", what am I doing wrong here?

"Fred Smith" wrote:

Insert a helper column. Enter the formula =proper(a1), and copy down. Copy the
new column, and Paste SpecialValues the old column. Delete the helper column.
Repeat for each column.

You will probably have to manually correct some names, especially last names
beginning with Mc and Mac.

--
Regards,
Fred


"dennis blaz" wrote in message
...
I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

Usually i wouldn't suggest this but i do it myself from time to time, copy
your rows, paste them into word, select them all and select Format-Change
Case-Title Case then copy and paste back into excel.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"dennis blaz" wrote:

Thanks Gord, but that's way too complicated for me. I think I'd rather just
manually change the few complex names and use an easier method.

"Gord Dibben" wrote:

To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and Mc's and
other stuff.


Gord Dibben MS Excel MVP

On Wed, 1 Aug 2007 20:44:01 -0700, dennis blaz
wrote:

I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

This could be dangerous code.

There may be formulas in the usedrange.

These will all get changed to values only.

Amend to this

Sub changer()
Dim cell
For Each cell In ActiveSheet.UsedRange
cell.Formula = WorksheetFunction.Proper(cell.Formula)
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 02 Aug 2007 01:43:09 -0700, "
wrote:

Actually, the VBA for this is VERY easy

Sub changer()
Dim cell
For Each cell In ActiveSheet.UsedRange
cell.Value = WorksheetFunction.Proper(cell.Value)
Next
End Sub

Changing cases to proper
 

I think a macro would be good if it only changed the selected cell of cells,
but not if they had formulas

At least it would be good for me

"John Bundy" (remove) wrote in message
...
Usually i wouldn't suggest this but i do it myself from time to time, copy
your rows, paste them into word, select them all and select Format-Change
Case-Title Case then copy and paste back into excel.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"dennis blaz" wrote:

Thanks Gord, but that's way too complicated for me. I think I'd rather
just
manually change the few complex names and use an easier method.

"Gord Dibben" wrote:

To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and
Mc's and
other stuff.


Gord Dibben MS Excel MVP

On Wed, 1 Aug 2007 20:44:01 -0700, dennis blaz
wrote:

I was sent an Excel spreadsheet with names and addresses, it's all in
upper
case and I want to change the entire document to "proper" case. Please
help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

Are you asking for a macro?

Sub optProper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord

On Mon, 6 Aug 2007 08:40:35 -0400, "politesse"
wrote:

I think a macro would be good if it only changed the selected cell of cells,
but not if they had formulas

At least it would be good for me

"John Bundy" (remove) wrote in message
...
Usually i wouldn't suggest this but i do it myself from time to time, copy
your rows, paste them into word, select them all and select Format-Change
Case-Title Case then copy and paste back into excel.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"dennis blaz" wrote:

Thanks Gord, but that's way too complicated for me. I think I'd rather
just
manually change the few complex names and use an easier method.

"Gord Dibben" wrote:

To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and
Mc's and
other stuff.


Gord Dibben MS Excel MVP

On Wed, 1 Aug 2007 20:44:01 -0700, dennis blaz
wrote:

I was sent an Excel spreadsheet with names and addresses, it's all in
upper
case and I want to change the entire document to "proper" case. Please
help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

Thanks Gord that works great


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Are you asking for a macro?

Sub optProper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord

On Mon, 6 Aug 2007 08:40:35 -0400, "politesse"

wrote:

I think a macro would be good if it only changed the selected cell of
cells,
but not if they had formulas

At least it would be good for me

"John Bundy" (remove) wrote in message
...
Usually i wouldn't suggest this but i do it myself from time to time,
copy
your rows, paste them into word, select them all and select
Format-Change
Case-Title Case then copy and paste back into excel.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"dennis blaz" wrote:

Thanks Gord, but that's way too complicated for me. I think I'd rather
just
manually change the few complex names and use an easier method.

"Gord Dibben" wrote:

To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and
Mc's and
other stuff.


Gord Dibben MS Excel MVP

On Wed, 1 Aug 2007 20:44:01 -0700, dennis blaz
wrote:

I was sent an Excel spreadsheet with names and addresses, it's all
in
upper
case and I want to change the entire document to "proper" case.
Please
help!

Ps. 861 names w/ 7 columns

Changing cases to proper
 

And thanks to David also.


Gord

On Mon, 6 Aug 2007 17:37:24 -0400, "politesse"
wrote:

Thanks Gord that works great


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Are you asking for a macro?

Sub optProper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord

On Mon, 6 Aug 2007 08:40:35 -0400, "politesse"

wrote:

I think a macro would be good if it only changed the selected cell of
cells,
but not if they had formulas

At least it would be good for me

"John Bundy" (remove) wrote in message
...
Usually i wouldn't suggest this but i do it myself from time to time,
copy
your rows, paste them into word, select them all and select
Format-Change
Case-Title Case then copy and paste back into excel.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"dennis blaz" wrote:

Thanks Gord, but that's way too complicated for me. I think I'd rather
just
manually change the few complex names and use an easier method.

"Gord Dibben" wrote:

To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and
Mc's and
other stuff.


Gord Dibben MS Excel MVP

On Wed, 1 Aug 2007 20:44:01 -0700, dennis blaz
wrote:

I was sent an Excel spreadsheet with names and addresses, it's all
in
upper
case and I want to change the entire document to "proper" case.
Please
help!

Ps. 861 names w/ 7 columns