Results 1 to 8 of 8

Make Text Proper/Sentence Case In VBA Module

This is a discussion on Make Text Proper/Sentence Case In VBA Module within the Excel Questions forums, part of the Question Forums category; Hi Gang, Can anyone suggest some handy code that I can put in a VBA module that will convert all ...

  1. #1
    New Member
    Join Date
    Jul 2003
    Posts
    1

    Default Make Text Proper/Sentence Case In VBA Module

    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

  2. #2

    Join Date
    Nov 2002
    Posts
    981

    Default Re: Make Text Proper/Sentence Case In VBA Module

    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

  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,244

    Default Re: Make Text Proper/Sentence Case In VBA Module

    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

  4. #4
    New Member
    Join Date
    Jun 2014
    Posts
    1

    Question Re: Make Text Proper/Sentence Case In VBA Module

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

    Quote Originally Posted by Smitty View Post
    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

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,308

    Default Re: Make Text Proper/Sentence Case In VBA Module

    Quote Originally Posted by Smitty View Post
    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Dec 2004
    Location
    Ribble Valley
    Posts
    549

    Default Re: Make Text Proper/Sentence Case In VBA Module

    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"
    Enjoy each day as though it were your last, one day it will be!
    Excel 2007

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,308

    Default Re: Make Text Proper/Sentence Case In VBA Module

    Quote Originally Posted by Titian View Post
    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


    Quote Originally Posted by satyad View Post
    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 by Rick Rothstein; Jun 26th, 2014 at 10:34 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  8. #8
    Board Regular
    Join Date
    Dec 2004
    Location
    Ribble Valley
    Posts
    549

    Default Re: Make Text Proper/Sentence Case In VBA Module

    Nice fix Rick - thanks
    Enjoy each day as though it were your last, one day it will be!
    Excel 2007

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com