changing text case

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda B - 28 Jan 2006 00:54 GMT

Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.

Reply to this Message

Anne Troy - 28 Jan 2006 01:02 GMT

See:
http://www.officearticles.com/excel/change_the_case_of_text_in_cells_in_microsof
t_excel.htm
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

> Is there a way to change the text case in Excel?
>
> Ex. from all caps to Sentence case, etc.

Reply to this Message

Linda B - 30 Jan 2006 18:41 GMT

Thanks. A simple answer. But I have not been able to figure out where to
enter and apply the formula. I am a novice at Excel. I have Excel 2003. I
read the help information on this subject, but cannot figure out what they
are telling me to do. I tried what I thought they were telling me to do, but
it did not work.

> See:
> http://www.officearticles.com/excel/change_the_case_of_text_in_cells_in_microsof
t_excel.htm
[quoted text clipped - 6 lines]
> >
> > Ex. from all caps to Sentence case, etc.

Reply to this Message

Bob Phillips - 28 Jan 2006 13:20 GMT

Excel supports conversion to upper, lower or proper case, but it has no
sentence case. I created this UDF to provide this

Private Function SentenceCase(ByVal para As String) As String
Dim oRegExp As Object
Dim oMatch As Object
Dim oAllMatches As Object

para = LCase(para)
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
oRegExp.Global = True
Set oAllMatches = oRegExp.Execute(para)
For Each oMatch In oAllMatches
With oMatch
Mid(para, .FirstIndex + 1 + .Length - 1, 1) = _
UCase(Mid(para, .FirstIndex + 1 + .Length - 1, 1))
End With
Next oMatch
SentenceCase = para
End Function

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> Is there a way to change the text case in Excel?
>
> Ex. from all caps to Sentence case, etc.

Reply to this Message

Linda B - 30 Jan 2006 18:41 GMT

Could you tell me where to enter this information? I have used Excel for
years but only on the very basic functions. i.e. name & address databases or
lists where I only have to sum the total on a column.

thanks.

> Excel supports conversion to upper, lower or proper case, but it has no
> sentence case. I created this UDF to provide this
[quoted text clipped - 29 lines]
> >
> > Ex. from all caps to Sentence case, etc.

Reply to this Message

David McRitchie - 31 Jan 2006 04:20 GMT

Hi Linda,
To install a macro posted in the newsgroup see
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

Some additional macros pertaining to your question
http://www.mvps.org/dmcritchie/excel/proper.htm

For the worksheet formulas they were pictured well on Anne's page
so I don't understand the problem with them. Column A is the
original data, and Column B is the helper column where you will
type in the formulas as shown, what you actually will see in Column B
is depicted in Column C. After putting the formula into B1 you
would use the fill handle to the formula down.
http://www.mvps.org/dmcritchie/excel/fillhand.htm
If you see your formula in column B instead of the result, check that
1) you don't have a space in front of the formula
2) you do *not* have the formula view checked in
tools, options, view(tab), (uncheck) formula view
3) that you have automatic calculation turned on --
tools, options, calculation (tab), calculation: automatic

But macro solutions or the addin with a selection would be faster than
using worksheet formulas so I really would not that method; however,
it is important that you understand the use of the formulas, and the
fill handle, so go through the exercise of using the formulas you asked for.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> Could you tell me where to enter this information? I have used Excel for
> years but only on the very basic functions. i.e. name & address databases or
[quoted text clipped - 35 lines]
> > >
> > > Ex. from all caps to Sentence case, etc.

Reply to this Message

Linda B - 31 Jan 2006 22:31 GMT

Thanks. I will read through your answer and do some testing of what I learn.

> Hi Linda,
> To install a macro posted in the newsgroup see
[quoted text clipped - 66 lines]
> > > >
> > > > Ex. from all caps to Sentence case, etc.

Reply to this Message

Shailesh Shah - 29 Jan 2006 07:13 GMT

Hi Linda,

You can download "change case in excel cells" (for excel-2000+) that have
word's change case interface from below site.

http://in.geocities.com/shahshaileshs/

With this, you can also
1. change case of selected text from the textbox in the com-addins
dialogbox.
2. change text case to sentence case.

Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)

----------------------------------------------------------------------------
-----------------

> Is there a way to change the text case in Excel?
>
> Ex. from all caps to Sentence case, etc.

Reply to this Message

Linda B - 30 Jan 2006 18:43 GMT

Perfect. I have a selection under format for change case now.

Would like to learn how to do it with formulas to stretch my knowledge, but
this is great. The simpler the fix the better.

> Hi Linda,
>
[quoted text clipped - 18 lines]
> >
> > Ex. from all caps to Sentence case, etc.

Reply to this Message