I have a huge database in excel.

Unfortunately, some of the entries are in ALL CAPS (E. MAIN ST or JOHN SMITH)

It is unfortunate as they need to be E. Main St or John Smith.

Is there any way to fix this without retyping every entry?

Try out the belowfunction
=PROPER(A1)

May be in Sheet2 cell A1 try =PROPER(sHEET1!a1) and copy down/across as
required and once complete copy>paste special>Values. to convert all formulas
to values

If this post helps click Yes
---------------
Jacob Skaria


"sos-DC" wrote:

> I have a huge database in excel.
>
> Unfortunately, some of the entries are in ALL CAPS (E. MAIN ST or JOHN SMITH)
>
> It is unfortunate as they need to be E. Main St or John Smith.
>
> Is there any way to fix this without retyping every entry?

On Thu, 19 Nov 2009 08:54:09 -0800, sos-DC
<(E-Mail Removed)> wrote:

>I have a huge database in excel.
>
>Unfortunately, some of the entries are in ALL CAPS (E. MAIN ST or JOHN SMITH)
>
>It is unfortunate as they need to be E. Main St or John Smith.
>
>Is there any way to fix this without retyping every entry?

If you use the function PROPER() you will get the result

E. Main St Or John Smith

which is not exactly what you want, but very close.

Hope this helps / Lars-Åke

Manually use the PROPER worksheet function.

Or install and run this macro.

Sub Proper_Case()
Dim rng As Range
Set rng = Nothing
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If rng Is Nothing Then
Else
rng.Formula = Application.Proper(rng.Formula)
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 19 Nov 2009 08:54:09 -0800, sos-DC <(E-Mail Removed)>
wrote:

>I have a huge database in excel.
>
>Unfortunately, some of the entries are in ALL CAPS (E. MAIN ST or JOHN SMITH)
>
>It is unfortunate as they need to be E. Main St or John Smith.
>
>Is there any way to fix this without retyping every entry?

Hi

This may works, try assuming cell A1 hold E.MAIN ST
place this formula in cell B1, PROPER(A1) and copy down to the last data
where column A is fill.
then select and copy the range in colmun B and do a paste special >> value
into the range in column A
--
Hope this help

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis





"sos-DC" wrote:

> I have a huge database in excel.
>
> Unfortunately, some of the entries are in ALL CAPS (E. MAIN ST or JOHN SMITH)
>
> It is unfortunate as they need to be E. Main St or John Smith.
>
> Is there any way to fix this without retyping every entry?