convert a column of text to proper

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

Guest

In a spreadsheet that I made, there is a column with all types of text: all
upper, all lower, proper. It could be a long task to re-type each cell.

I'd like to convert this entire column to all proper. How can this be easily
done?

Thanks in advance for your help.
 
Hi Harvey

it can be done via the use of a helper column
insert a column to the right of your column
then type the following formula in cell 1 of the new column
=Proper(A1)
where A is the column letter of the column you want to fix
move your cursor over the bottom right hand corner of this cell and double
click
the formula should be filled down the column.
now select this new column and choose copy
click on the column header of the original column and choose
edit / paste special - values
you can now delete the helper column.

hope this helps
Cheers
JulieD
 
Harvey

Assuming your column is A.

In an adjacent column enter =PROPER(A1) in a cell and double-click on the
fill handle of that cell to copy the formula down.

Fill handle is the black lump at right bottom corner of the cell.

When happy with results, copy and paste special(in place)>values>OK>Esc

You can now delete the original column.

Gord Dibben Excel MVP
 
Create a helper column next to your column.
If your data is in column A starting in A1, put =Proper(A1) in B1 and copy
it down. Then, copy the new cells and go to Edit | Paste Special | Values.
Then, delete the original column.

tj
 
this may prove useful
Sub ChangeCase()
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
 
Back
Top