<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Change Text to Upper Case or Proper Case

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Change Text to Upper Case or Proper Case. Convert Text to Proper Case or Upper Case

Current Special! Complete ExcelExcel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 InstantBuy/Download

Change Text to Upper Case or Proper Case. See Also:Force Upper Case/Proper Case

Excel has 2 built in functions for converting text to either UPPER CASE or Proper Case. The 2 functions that do this are shown below;

=UPPER(A1)
=PROPER(A1)

These Excel functions work well when referring to cells that house the text. However, there are many instances when using the Worksheet Function approach is not practical. The Excel macro code below can be used to change existing text to either UPPER CASE or Proper Case. If you run the macro with only a single cell selected it will work on the entire Worksheet. If you run the macro withmore than 1 cell selected it will work on only your selection. The other settings that the StrConv Function take are shown below. See the Excel VBA help for specifics.

vbUpperCase = Converts the string to uppercase characters.
vbLowerCase = Converts the string to lowercase characters.
vbProperCase = Converts the first letter of every word in string to uppercase.
vbWide = Converts narrow (single-byte) characters in string to wide (double-byte) characters.
vbNarrow = Converts wide (double-byte) characters in string to narrow (single-byte) characters.
vbKatakana = Converts Hiragana characters in string to Katakana characters.
vbHiragana = Converts Katakana characters in string to Hiragana characters.
vbUnicode = Converts the string to Unicode using the default code page of the system. (Not available on the Macintosh.)
vbFromUnicode = Converts the string from Unicode to the default code page of the system. (Not available on the Macintosh.)

Sub ConvertCase()Dim rAcells As Range, rLoopCells As RangeDim lReply As Long    'Set variable to needed cells    If Selection.Cells.Count = 1 Then        Set rAcells = ActiveSheet.UsedRange    Else       Set rAcells = Selection    End If    On Error Resume Next 'In case of NO text constants.    'Set variable to all text constants    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)        If rAcells Is Nothing Then       MsgBox "Could not find any text."       On Error GoTo 0       Exit Sub    End If              lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _    vbYesNoCancel, "OzGrid.com")    If lReply = vbCancel Then Exit Sub      If lReply = vbYes Then ' Convert to Upper Case          For Each rLoopCells In rAcells              rLoopCells = StrConv(rLoopCells, vbUpperCase)          Next rLoopCells    Else ' Convert to Proper Case          For Each rLoopCells In rAcells              rLoopCells = StrConv(rLoopCells, vbProperCase)          Next rLoopCells    End If    End Sub

See Also: Force Upper Case/Proper Case

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft