changing text case

[Linda B]

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

Ex. from all caps to Sentence case, etc.

[Anne Troy]

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

"Linda B" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to change the text case in Excel?
>
> Ex. from all caps to Sentence case, etc.

[Bob Phillips]

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)

"Linda B" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to change the text case in Excel?
>
> Ex. from all caps to Sentence case, etc.

[Shailesh Shah]

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)


----------------------------------------------------------------------------
-----------------
Linda B <[email protected]> wrote in message
news:[email protected]...
> Is there a way to change the text case in Excel?
>
> Ex. from all caps to Sentence case, etc.

[Linda B]

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.

"Anne Troy" wrote:

> See:
> http://www.officearticles.com/excel/...soft_excel.htm
> ************
> Hope it helps!
> Anne Troy
> www.OfficeArticles.com
>
> "Linda B" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a way to change the text case in Excel?
> >
> > Ex. from all caps to Sentence case, etc.
>
>
>

[Linda B]

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.

"Bob Phillips" wrote:

> 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)
>
> "Linda B" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a way to change the text case in Excel?
> >
> > Ex. from all caps to Sentence case, etc.
>
>
>

[Linda B]

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.



"Shailesh Shah" wrote:

> 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)
>
>
> ----------------------------------------------------------------------------
> -----------------
> Linda B <[email protected]> wrote in message
> news:[email protected]...
> > Is there a way to change the text case in Excel?
> >
> > Ex. from all caps to Sentence case, etc.
>
>
>

[David McRitchie]

Hi Linda,
To install a macro posted in the newsgroup see
http://www.mvps.org/dmcritchie/excel....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

"Linda B" <[email protected]> wrote in message news:[email protected]...
> 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.
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "Linda B" <[email protected]> wrote in message
> > news:[email protected]...
> > > Is there a way to change the text case in Excel?
> > >
> > > Ex. from all caps to Sentence case, etc.
> >
> >
> >

[Linda B]

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

"David McRitchie" wrote:

> Hi Linda,
> To install a macro posted in the newsgroup see
> http://www.mvps.org/dmcritchie/excel....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
>
> "Linda B" <[email protected]> wrote in message news:[email protected]...
> > 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.
> >
> > "Bob Phillips" wrote:
> >
> > > 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)
> > >
> > > "Linda B" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Is there a way to change the text case in Excel?
> > > >
> > > > Ex. from all caps to Sentence case, etc.
> > >
> > >
> > >
>
>
>