Changing case on mailing lists I receive in Excel 2003?
I receive monthly mailing lists from my local chamber of commerce and
magazines from which I advertise. The lists are in Excel format and are always in all upper case. Before I can prepare the mailings, I have to retype the addresses. Sometimes there can be over 500 on each list. Is there a way to get Excel to do this for me? I've tried the Proper function, but it only works one cell at a time and between the click/copy/paste that I have to do, it is no improvement. Is there a way to replicate the Proper function across the spreadsheet? |
Hi Walt
The best thing to do is use a macro See this webpages http://www.mvps.org/dmcritchie/excel/proper.htm Or http://www.cpearson.com/excel/case.htm Here are some Macro's for changing text cells in the selection Sub Uppercase_macro() Dim selectie As Range Dim cel As Range On Error Resume Next Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants, xlTextValues) If selectie Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each cel In selectie cel.Value = UCase(cel.Value) Next cel Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Sub Lowercase_macro() Dim selectie As Range Dim cel As Range On Error Resume Next Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants, xlTextValues) If selectie Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each cel In selectie cel.Value = LCase(cel.Value) Next cel Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Sub Propercase_macro() Dim selectie As Range Dim cel As Range On Error Resume Next Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants, xlTextValues) If selectie Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each cel In selectie cel.Value = StrConv(cel.Value, vbProperCase) Next cel Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Walt" wrote in message ... I receive monthly mailing lists from my local chamber of commerce and magazines from which I advertise. The lists are in Excel format and are always in all upper case. Before I can prepare the mailings, I have to retype the addresses. Sometimes there can be over 500 on each list. Is there a way to get Excel to do this for me? I've tried the Proper function, but it only works one cell at a time and between the click/copy/paste that I have to do, it is no improvement. Is there a way to replicate the Proper function across the spreadsheet? |
All times are GMT +1. The time now is 03:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com