how do I change case in an entire column of text in Excel

L

linpengie

I have imported data from an FMPro Database which is already in Title Case
into Excel. When it imported, it became Upper case. since this data is to
be used in a mail merge I would prefer to be able to change the case in the
entire column to Title Case. I believe my problem could be solved with a
formula, but can't figure out how to make it work. I checked Knowledge Base
and Help but can't find what I need. As I'm a novice with formulas I need an
EASY solution. Help!
 
D

Dave R.

The easiest way (you asked for it!) would be to use a 'helper' column with
the formula

=proper(a1)

and copying the formula down until all your columns data is in this new
column in proper case.
Then, copy this entire column and paste special (as values) over the range
of the original, upper-case data. Make a backup just to be safe before you
do this.
 
R

Ramakrishnan Rajamani

From what I understand, it looks like you are trying to convert the initial
letters (seperated by spaces) to capitals. There is an inbuilt function
=Proper(text) which could be tried out.
 
L

linpengie

that's the function I tried to use and it didn't work, unless I understood it
improperly. What I did was highlight the whole column then attempt to apply
the function. It didn't work.
so now what?
lin
 
L

linpengie

I will try this and see what happens. If anyone has other solutions, I'd be
much obliged
lin
 
D

Dave R.

You may have understood it (proper) properly (?) but you used it improperly.
Highlighting a column of data and entering a formula on top of it wont do
anything but overwrite the contents of the top cell which should make it
blatantly obvious that it's not the right way to do it since you're over
writing the data you want the function to work on.

This is why you use the formula in a separate range and reference the cells
containing the data you want to do work on.
 
L

linpengie

great, thanks

Dave R. said:
You may have understood it (proper) properly (?) but you used it improperly.
Highlighting a column of data and entering a formula on top of it wont do
anything but overwrite the contents of the top cell which should make it
blatantly obvious that it's not the right way to do it since you're over
writing the data you want the function to work on.

This is why you use the formula in a separate range and reference the cells
containing the data you want to do work on.
Click to expand...
 
G

Gord Dibben

No other solutions than =PROPER(A1) as you have been given unless you opt for
VBA macro which can operate on a selected range without the need for formulas.

Sub Proper_Case()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = Application.Proper(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP
 
Top