Changing the case of text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help... I have a spreadsheet that contains names and addresses. Some of the
cells have text entered in all caps. I want to change it to PROPER text
(first letter in caps only) but I am having trouble following the Help menu
steps.
 
The PROPER function is like any other formula. The format of the formula
is:

=PROPER()

If all of the text is in one column, you can probably insert a new
column next to it and enter the formula in the first cell of the new
column next to the first cell that needs to be changed. If it is cell
B2, you formula would be in C2 as the following:

=PROPER(B2)

Tab out of C2 and it should now show the text in the desired format in
the new cell (C2). You can then copy the formula from that cell and
past it to the remaining cells in the column and the case will now be
converted.

BEFORE DELETING the first column, you will need to select the first
column> Edit> Copy. With the whole column highlighted, right click on
it and select "Paste Special". Select the "Values" button and click OK.
The formulas in that column will now be replaced with the text and you
will be able to delete the original column.

Hope that helps!
 
Hi!

Insert a helper column next to the column that contains the names. If the
range of names in the original column start in say, cell A1, in the new
helper column which is now column B, enter this formula in B1:

=PROPER(A1)

Copy down as needed or just double click the fill handle. Now, select the
range of names in column B. Then do EDIT>COPY, EDIT>PASTE SPECIAL>VALUES>OK.

Now you can delete the original list of names in column A, or even delete
column A if there is no more data beyond the list of names.

Biff
 
try this macro. Select the area to change and then fire this.

Sub ChangeCase() 'Don Guillett
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub
 
Back
Top