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!

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.


"linpengie" wrote in message
...
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!

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.

"linpengie" wrote:

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!

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

"Ramakrishnan Rajamani" wrote:

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.

"linpengie" wrote:

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!

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

"Dave R." wrote:

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.


"linpengie" wrote in message
...
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!

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.



"linpengie" wrote in message
...
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

"Ramakrishnan Rajamani" wrote:

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.

"linpengie" wrote:

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!

great, thanks

"Dave R." wrote:

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.



"linpengie" wrote in message
...
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

"Ramakrishnan Rajamani" wrote:

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.

"linpengie" wrote:

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!

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

On Wed, 30 Mar 2005 09:05:06 -0800, linpengie
wrote:

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

"Dave R." wrote:

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.


"linpengie" wrote in message
...
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!

thanks!
lin

"Gord Dibben" wrote:

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

On Wed, 30 Mar 2005 09:05:06 -0800, linpengie
wrote:

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

"Dave R." wrote:

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.


"linpengie" wrote in message
...
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!

You're welcome and thanks for the feedback.

Also thanks to David McRitchie for the code.


Gord Dibben Excel MVP

On Thu, 31 Mar 2005 09:23:06 -0800, linpengie
wrote:

thanks!
lin

"Gord Dibben" wrote:

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

On Wed, 30 Mar 2005 09:05:06 -0800, linpengie
wrote:

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

"Dave R." wrote:

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.


"linpengie" wrote in message
...
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!