changing text case

  • Thread starter Thread starter Guest
  • Start date Start date
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)
 
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.
 
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 said:
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 said:
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.
Click to expand...
Click to expand...
 
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,
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

Linda B said:
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 said:
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 said:
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.
Click to expand...
Click to expand...
Click to expand...
 
Thanks. I will read through your answer and do some testing of what I learn.

David McRitchie said:
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

Linda B said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top