Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    New Hampshire
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change text case (Excel 2000)

    Does anyone know of a macro to change text case (capitalize, first letter of a word, etc.) that mimics the SHIFT F3 function in Word? Thanks in advance for any help with this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change text case (Excel 2000)

    Here is a rather crude macro. Perhaps you or another lounger can use it as a starting point.
    It checks the first two characters of each cell in the selection to decide how to modify capitalization.
    If the first characters aren't alphabetic, it changes the value to upper case - this clearly leaves room for improvement.

    You can assign the macro to a keyboard shortcut and/or toolbar button.

    Note: Shift+F3 is already taken: it inserts a function into a formula. Alt+F3 is free.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub ChangeCase()
    Dim rng As Range
    Dim opt As Integer
    Dim str As String
    Dim asc1 As Byte
    Dim asc2 As Byte
    For Each rng In Selection
    str = Trim(rng.Value)
    If str <> "" Then
    If rng.HasFormula = False Then
    opt = 0
    asc1 = Asc(Left(str, 1))
    If Len(str) = 1 Then
    If asc1 >= 97 And asc1 <= 122 Then
    ' current: lower case, switch to: proper case
    opt = vbProperCase
    ElseIf asc1 >= 65 And asc1 <= 90 Then
    ' current: upper case, switch to: lower case
    opt = vbLowerCase
    End If
    Else
    asc2 = Asc(Mid(str, 2, 1))
    If asc1 >= 97 And asc1 <= 122 Then
    ' current: lower case, switch to: proper case
    opt = vbProperCase
    ElseIf asc1 >= 65 And asc1 <= 90 Then
    If asc2 >= 97 And asc2 <= 122 Then
    'current: proper case, switch to: upper case
    opt = vbUpperCase
    ElseIf asc2 >= 65 And asc2 <= 90 Then
    ' current: upper case, switch to: lower case
    opt = vbLowerCase
    End If
    End If
    End If
    If opt = 0 Then
    opt = vbUpperCase
    End If
    rng.Value = StrConv(rng.Value, opt)
    End If
    End If
    Next
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change text case (Excel 2000)

    If the text is in A1, try:

    <pre>=Proper(A1)
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Change text case (Excel 2000)

    See also Brooke's <!post=Star Post,51226>Star Post<!/post> and WebGenii's <!post=Toggle Macro,69696>Toggle Macro<!/post> in that same thread; the latter works the same as Word's Shift-F3.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change text case (Excel 2000)

    Hello Gang,

    This little help file is from my friends at a company called Hospitality 1ST. It's an Excel add-in (which means once you have the appropriate ".xla" file just go to Tools>Add-Ins and search for the corresponding file to load). Here is the direction to the file(s) you can use (two of them).

    <<< Newsletter Excerpt >>>
    Many may believe, like myself, that MS-Office has all the tools needed for database management - it's a matter of knowing what's included and taking the time to use the features. EXCEL is incredibly powerful but lacks a "change case" feature (such as that found with MS-Word) from the format toolbar. We're providing the help (and files) needed to im- plement the change case feature. "chngca.xla" simply adds the command (to format menu) whenever you run it - it's not "sticky" i.e. it drops off once you close EXCEL. http://hospitality-1st.com/help/chngca.xla is the file you'll save to your hard drive, and then "double-click" to install. "chngcase.exe" is a package of three files, one a readme.txt, one the setup file and one the actual macro that does the work so many of us have been looking for. It is "sticky" i.e. stays with EXCEL program without dropping off after each session. Once you run the setup (it takes a few seconds to load) the format menu permanently contains a Change Case selection which is also available from the "right-mouse click" command menu. http://hospitality-1st.com/help/chngcase.exe is the file you'll download. Be sure to (read) the "readme.txt" file for complete info (it's a big help). Both files are freeware. If you're on a network be sure to check with your network admin before downloading and/or using either of these files. We do encourage the use of a virus scan if you decide to pass-a-long the files to co-workers and friends.
    <<< end excerpt >>>

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Change text case (Excel 2000)

    <P ID="edit" class=small>Edited by DoryO on 19-Jun-02 16:22.</P>Sub Proper_Case()
    ' Loop to cycle through each cell in the specified range.
    ' There is not a Proper function in Visual Basic for Applications.
    ' So, you must use the worksheet function in the following form:

    For Each x In Selection.Cells
    x.Formula = Application.Proper(x.Formula)
    Next

    End Sub
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

Posting Permissions

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