Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
changing text case
Is there a way to change the text case in Excel?
Ex. from all caps to Sentence case, etc. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 ... 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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 ... 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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop for VBA code? | Excel Worksheet Functions | |||
convert numbers to text | Excel Discussion (Misc queries) | |||
excel | Excel Worksheet Functions | |||
Spellnumber | Excel Worksheet Functions | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) |