Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 623
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 244
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 136
Default 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




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 772
Default 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



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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


  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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






  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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




  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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






  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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






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
Changing Entries to Proper and Upper Case Q Sean Excel Worksheet Functions 4 April 1st 07 03:46 PM
Creating an changing scale on x axis for outlying cases Steve BBC UK Charts and Charting in Excel 1 December 12th 06 02:24 PM
Macro for changing text to Proper Case JPriest Excel Worksheet Functions 3 August 8th 05 09:31 PM
Changing proper fractions to improper. apersonoutthere Excel Discussion (Misc queries) 1 April 15th 05 07:24 AM
Changing Upper case to Proper Case Mountain Excel Worksheet Functions 1 January 13th 05 11:37 PM


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"