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.

try

= proper(a1)

If you need a macro to automate this provide more detail.

Mike

"sos-DC" wrote:

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.


"sos-DC" wrote:

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.

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

On Nov 5, 3:49*pm, sos-DC wrote:
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.

Thank you for your help on this.

"JLatham" wrote:

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" wrote:

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.

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)

"Mike H" wrote:

try

= proper(a1)

If you need a macro to automate this provide more detail.

Mike

"sos-DC" wrote:

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.

Thank you

"Pete_UK" wrote:

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

On Nov 5, 3:49 pm, sos-DC wrote:
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.

.

IGNORE MY PREVIOUS COMMENT.

=proper(B2) works GREAT!

Another question.

Is there a way to easily change change mixed case to all caps?


"Mike H" wrote:

try

= proper(a1)

If you need a macro to automate this provide more detail.

Mike

"sos-DC" wrote:

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.

=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


"sos-DC" wrote in message
...
IGNORE MY PREVIOUS COMMENT.

=proper(B2) works GREAT!

Another question.

Is there a way to easily change change mixed case to all caps?


"Mike H" wrote:

try

= proper(a1)

If you need a macro to automate this provide more detail.

Mike

"sos-DC" wrote:

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.

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


Gord Dibben MS Excel MVP

On Thu, 19 Nov 2009 09:08:13 -0800, sos-DC
wrote:

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)

"Mike H" wrote:

try

= proper(a1)

If you need a macro to automate this provide more detail.

Mike

"sos-DC" wrote:

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.

Great! Thanks a lot.

"Peo Sjoblom" wrote:

=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


"sos-DC" wrote in message
...
IGNORE MY PREVIOUS COMMENT.

=proper(B2) works GREAT!

Another question.

Is there a way to easily change change mixed case to all caps?


"Mike H" wrote:

try

= proper(a1)

If you need a macro to automate this provide more detail.

Mike

"sos-DC" wrote:

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.


.