Make Text Proper/Sentence Case In VBA Module

Kenny_UK

New Member
Joined
Jul 29, 2003
Messages
1
Hi Gang,

Can anyone suggest some handy code that I can put in a VBA module that will convert all text within a Spreadsheet to Proper or Sentence like this ---> Hello Everyone, Hope You Are All Happy.

Any help or suggestions would be great,

Thank You in advance,

Kenny :biggrin:
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Dim rng As Range, cell As Range
Set rng = Cells.SpecialCells(xlCellTypeConstants, 2)
For Each cell In rng
cell.Value = StrConv(cell.Value, vbProperCase)
Next
 
Upvote 0
Here's a neat utility from Ivan F. Moala

Sub TextCon()
'code by Ivan
Dim ocell As Range, ans As String

ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub

Hope that helps,

Smitty
 
Upvote 0
Pl explain me how to use this formula to change case in excel?

Here's a neat utility from Ivan F. Moala

Sub TextCon()
'code by Ivan
Dim ocell As Range, ans As String

ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub

Hope that helps,

Smitty
 
Upvote 0
Here's a neat utility from Ivan F. Moala

Sub TextCon()
'code by Ivan
Dim ocell As Range, ans As String

ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub
The highlighted text can be replaced with the following native VB function call...

StrConv(ocell.Text, vbProperCase)
 
Upvote 0
It doesn't appear to function correctly where there two sentences when using "S" option

e.g. "this is my first text. this is my second text" - the result is, "This is my first text. this is my second text"
 
Upvote 0
It doesn't appear to function correctly where there two sentences when using "S" option

e.g. "this is my first text. this is my second text" - the result is, "This is my first text. this is my second text"
This modification of the macro should fix that problem...
Code:
Sub TextCon()
  
  Dim X As Long, oCell As Range, Ans As String, Sentences() As String
  
  Ans = Application.InputBox("Type in Letter" & vbCr & _
        "(L)owercase, (U)ppercase, (S)entence, (T)itles ")
  
  If Ans = "" Then Exit Sub
  
  For Each oCell In Selection.SpecialCells(xlCellTypeConstants, 2)
    Select Case UCase(Ans)
      Case "L": oCell = LCase(oCell.Text)
      Case "U": oCell = UCase(oCell.Text)
      Case "T": oCell = StrConv(oCell.Text, vbProperCase)
      Case "S": Sentences = Split(oCell, ".")
                For X = 0 To UBound(Sentences)
                  Sentences(X) = " " & UCase(Left(LTrim(Sentences(X)), 1)) & _
                                 LCase(Mid(LTrim(Sentences(X)), 2))
                Next
                oCell = Trim(Join(Sentences, "."))
    End Select
  Next
  
 End Sub



Pl explain me how to use this formula to change case in excel?
It is not a formula, rather, it is a macro the physically changes the values it is given to work on. To do that, select the cells you want to change, press ALT+F8 to bring up the Macro dialog box, select TextCon from the list and then click the Run button.
 
Last edited:
Upvote 0
I know this is really old but how about a function version?
Code:
Function sentcase(oCell As Range)
Dim Sentences() As String
Sentences = Split(oCell, ".")
                For X = 0 To UBound(Sentences)
                  Sentences(X) = " " & UCase(Left(LTrim(Sentences(X)), 1)) & _
                                 LCase(Mid(LTrim(Sentences(X)), 2))
                                 
                Next
                              
                sentcase = Trim(Join(Sentences, "."))
 End Function
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top