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

Convert Excel Text To Upper Case

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

EXCEL VBA: Changing text case

We'll look at UPPER CASE, Proper Case and lower case. UPPER CASE is the fastest because we can use the Replace Method, like this;

Sub UPPERCASE()Dim lChr As LongWith Selection   For lChr = 97 To 122      .Replace Chr(lChr), UCase(Chr(lChr))   Next lChrEnd WithEnd Sub

If you are wondering how this works, let me explain. Chr(97) in VBA results in a lower case "a". Chr(98)="b", chr(99)="c" and so on....The Ucase Function is used to convert all lower case letters to UPPER CASE. "a" becomes "A" and so on...

For all other cases, we can use the StrConv Function, which can convert according to the table below;

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

The code we can use is;

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 lower case or 'No' for Proper Case.", _    vbYesNoCancel, "OzGrid.com")    If lReply = vbCancel Then Exit Sub    If lReply = vbYes Then ' Convert to lower case          For Each rLoopCells In rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)              rLoopCells = StrConv(rLoopCells, vbLowerCase)          Next rLoopCells    Else ' Convert to Proper Case         For Each rLoopCells In rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)              rLoopCells = StrConv(rLoopCells, vbProperCase)          Next rLoopCells    End IfEnd Sub

Back to: Excel Custom Function/Formulas

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