Change text case (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

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 = 65 And asc1 = 97 And asc1 = 65 And asc1 = 97 And 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

Reply | Quote

If the text is in A1, try:

=Proper(A1)

Reply | Quote

See also Brooke’s Star Post and WebGenii’s Toggle Macro in that same thread; the latter works the same as Word’s Shift-F3.

Reply | Quote

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).

<<>>
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.
<<>>

Reply | Quote

Edited by DoryO on 19-Jun-02 16:22.

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

Reply | Quote