changing case

  • Thread starter Thread starter sos-DC
  • Start date Start date
S

sos-DC

I have a database of over 1,700 line in which more than half have addresses
in ALL upper case.

I need an easy way to change MAIN STREET to Main Street without having to
retype most of the database.

Can this be done?

Thanks.
 
Assuming that addresses are in column C for this example and that the first
one is in row 2 (at C2 in other words).

In any available column on the same sheet, on the first row enter this formula
=Proper(C2)
then fill that formula down for all 1700 rows. The conversion won't be
perfect, words like MCCAMPBELL will come out like Mccampbell and NW would end
up Nw, but it's a start.

After that, select all of those cells and use [Edit] --> [Copy]
Go to the first cell that needs the change applied to (C2) and then use
[Edit] --> Paste Special with the "Values" option checked. Click OK. You
can now do away with the added column of formulas, or change them to work
with another "original" column in the same manner.
 
In a helper column you could have this formula:

=PROPER(B2)

assuming your street name starts in cell B2.

Copy this down as far as required, and then fix the values (select all
the cells with the formula in, click <copy>, then right-click and
select Paste Special | Values (check) | OK then <Esc>). Now you can
copy/paste the values from the helper column to overwrite the
originals in column B, and then delete the helper column.

Hope this helps.

Pete
 
Thank you for your help on this.

JLatham said:
Assuming that addresses are in column C for this example and that the first
one is in row 2 (at C2 in other words).

In any available column on the same sheet, on the first row enter this formula
=Proper(C2)
then fill that formula down for all 1700 rows. The conversion won't be
perfect, words like MCCAMPBELL will come out like Mccampbell and NW would end
up Nw, but it's a start.

After that, select all of those cells and use [Edit] --> [Copy]
Go to the first cell that needs the change applied to (C2) and then use
[Edit] --> Paste Special with the "Values" option checked. Click OK. You
can now do away with the added column of formulas, or change them to work
with another "original" column in the same manner.


sos-DC said:
I have a database of over 1,700 line in which more than half have addresses
in ALL upper case.

I need an easy way to change MAIN STREET to Main Street without having to
retype most of the database.

Can this be done?

Thanks.
Click to expand...
Click to expand...
 
Thank you, I'll try that.

(do you know how handles the date/time for postings? Today is Nov 19 but it
says I posted this on Nov. 5)
 
Thank you

Pete_UK said:
In a helper column you could have this formula:

=PROPER(B2)

assuming your street name starts in cell B2.

Copy this down as far as required, and then fix the values (select all
the cells with the formula in, click <copy>, then right-click and
select Paste Special | Values (check) | OK then <Esc>). Now you can
copy/paste the values from the helper column to overwrite the
originals in column B, and then delete the helper column.

Hope this helps.

Pete



.
Click to expand...
 
IGNORE MY PREVIOUS COMMENT.

=proper(B2) works GREAT!

Another question.

Is there a way to easily change change mixed case to all caps?
 
=UPPER(B2)

or use some code like


Option Explicit
Sub Upper_Case()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Formula = UCase(Cell.Formula)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub




Here's code for proper case




Option Explicit
Sub PropCase()
Application.DisplayAlerts = False
Dim R As Range
For Each R In Selection.Cells
If R.HasFormula Then
R.Formula = "=PROPER(" & Mid(R.Formula, 2) & ")"
Else
R.Value = Application.Proper(R.Value)
End If
Next
Application.DisplayAlerts = True
End Sub



Select the range and run the macro

--


Regards,


Peo Sjoblom
 
You did post the same message on Nov 5th and received replies at that time.


Gord Dibben MS Excel MVP
 
Great! Thanks a lot.

Peo Sjoblom said:
=UPPER(B2)

or use some code like


Option Explicit
Sub Upper_Case()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Formula = UCase(Cell.Formula)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub




Here's code for proper case




Option Explicit
Sub PropCase()
Application.DisplayAlerts = False
Dim R As Range
For Each R In Selection.Cells
If R.HasFormula Then
R.Formula = "=PROPER(" & Mid(R.Formula, 2) & ")"
Else
R.Value = Application.Proper(R.Value)
End If
Next
Application.DisplayAlerts = True
End Sub



Select the range and run the macro

--


Regards,


Peo Sjoblom





.
Click to expand...
 
Back
Top