How do I change from all caps to proper case?

[Guest]

I purchased a rather large list that is in all caps (in .txt format). Is
there a function that will allow Excel (or any other Office product) to
automatically change the names to proper case? I will need to save the
results back in .txt format to import into a contact management program.

 

[JE McGimpsey]

take a look here:

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

 

[Guest]

=propoer(A1) in excel
or open in Word andFormat>Change Case

 

[Guest]

I have tried this and it doesn't seem to work.
Where do I put the =proper(A1) ... in the column marked A1 or for the whole
column?

 

[George Nicholson]

- Make a backup!
- Add a new column (lets say it's column B)
- In B1 type: = Proper(A1)
- B1 should now reflect how you want the data in A1 to appear
- Copy B1 down as far as your list goes. Calculate (F9), if necessary.
- Select Column B and Copy it.
- With Column B still selected: Edit>PasteSpecial>Values (this replaces the
formula with the formula results)
- After verifying that Column B is what you want and has no loss of data,
you can delete Column A

HTH,

 

[Guest]

Copy the following macro:

Sub Change_Case()
Dim ocell As Range
Dim Ans As String
Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")
If Ans = "" Then Exit Sub
For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub