Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 28th 06, 01:54 AM posted to microsoft.public.excel.newusers
Linda B
 
Posts: n/a
Default changing text case

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

Ex. from all caps to Sentence case, etc.

  #2   Report Post  
Old January 28th 06, 02:02 AM posted to microsoft.public.excel.newusers
Anne Troy
 
Posts: n/a
Default changing text case

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

"Linda B" wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.



  #3   Report Post  
Old January 28th 06, 02:20 PM posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default changing text case

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" wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.



  #4   Report Post  
Old January 29th 06, 08:13 AM posted to microsoft.public.excel.newusers
Shailesh Shah
 
Posts: n/a
Default changing text case

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 wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.



  #5   Report Post  
Old January 30th 06, 07:41 PM posted to microsoft.public.excel.newusers
Linda B
 
Posts: n/a
Default changing text case

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/...ft_exce l.htm
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Linda B" wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.






  #6   Report Post  
Old January 30th 06, 07:41 PM posted to microsoft.public.excel.newusers
Linda B
 
Posts: n/a
Default changing text case

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" wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.




  #7   Report Post  
Old January 30th 06, 07:43 PM posted to microsoft.public.excel.newusers
Linda B
 
Posts: n/a
Default changing text case

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 wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.




  #8   Report Post  
Old January 31st 06, 05:20 AM posted to microsoft.public.excel.newusers
David McRitchie
 
Posts: n/a
Default changing text case

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" wrote in message news
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" wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.






  #9   Report Post  
Old January 31st 06, 11:31 PM posted to microsoft.public.excel.newusers
Linda B
 
Posts: n/a
Default changing text case

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" wrote in message news
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" wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop for VBA code? paulinoluciano Excel Worksheet Functions 5 December 28th 05 02:30 PM
convert numbers to text bellman Excel Discussion (Misc queries) 0 October 4th 05 10:28 PM
excel melissa Excel Worksheet Functions 2 August 19th 05 09:46 PM
Spellnumber Norman Jones Excel Worksheet Functions 6 December 13th 04 08:21 AM
Identifying the Active Fill Color Steve Conary Excel Discussion (Misc queries) 3 December 9th 04 05:45 AM


All times are GMT +1. The time now is 07:01 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017