To convert text to lowercase in Excel VBA, use the LCase function: LCase("HELLO") returns "hello". For more control, such as converting only the first letter of each word to uppercase, use StrConv with the vbProperCase argument. This article explains both functions, how to apply them to ranges, and common pitfalls to avoid.
Understanding VBA String Case Functions
VBA provides three built-in functions for changing the case of a string:
LCase(string)– converts all letters to lowercase.UCase(string)– converts all letters to uppercase.StrConv(string, conversion)– offers more options, including proper case (first letter of each word capitalized) and Unicode conversions.
For example, LCase("Excel VBA") returns "excel vba". UCase("Excel VBA") returns "EXCEL VBA". StrConv("excel vba", vbProperCase) returns "Excel Vba" (note: it capitalizes the first letter of each word, but does not lowercase the rest, so “VBA” becomes “Vba” – a common gotcha).
Using LCase on a Range of Cells
To convert an entire column or range to lowercase, loop through the cells and apply LCase. Here is a simple macro that converts all text in column A (starting at A1) to lowercase:
Sub ConvertRangeToLower()
Dim cell As Range
' Save a copy of your workbook before running a macro
For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If Not IsEmpty(cell) Then
cell.Value = LCase(cell.Value)
End If
Next cell
End Sub
This code finds the last used row in column A and loops through each cell. The IsEmpty check avoids errors on blank cells. Remember that macros cannot be undone with Ctrl+Z, so always test on a copy of your data.
Using StrConv for Proper Case and More
StrConv is more flexible. The most common conversion constants are:
vbUpperCase(1) – same asUCase.vbLowerCase(2) – same asLCase.vbProperCase(3) – capitalizes the first letter of each word.
Example: StrConv("hello WORLD", vbProperCase) returns "Hello World". However, as noted, it does not lowercase the remainder of each word, so StrConv("eXCEL vBA", vbProperCase) returns "EXCEL Vba". If you need true title case (where only the first letter of each word is uppercase and the rest lowercase), you must combine functions: StrConv(LCase("eXCEL vBA"), vbProperCase) returns "Excel Vba" – still not perfect for acronyms, but better.
How to Choose Between LCase and StrConv
Use LCase when you simply need all lowercase text – it is faster and clearer. Use StrConv when you need proper case (like for names or titles) or when you need to convert between Unicode and ANSI (using vbUnicode and vbFromUnicode). For most spreadsheet tasks, LCase is sufficient.
Common Problems and Pitfalls
Here are issues users often encounter:
- Numbers and special characters:
LCaseandUCaseonly affect letters; numbers and punctuation remain unchanged. This is usually desired. - Non-English characters: VBA’s
LCaseworks with the system’s locale. For example, in a Turkish locale, the uppercase of ‘i’ is ‘İ’ (dotted capital I), which may not be what you expect. If you need consistent behavior across locales, consider usingWorksheetFunctionor a custom mapping. - Formulas vs. values: If a cell contains a formula,
cell.Value = LCase(cell.Value)will replace the formula with its lowercase result. To preserve formulas, you need to modify the formula string itself (e.g., wrap it inLOWER), which is more complex. - Entire column conversion: Converting a large range (e.g., 100,000 rows) with a loop can be slow. A faster method is to use an array: read the range into an array, convert each element, and write back. Example:
Sub FastLower()
Dim data As Variant
Dim i As Long, j As Long
' Save a copy first
data = Range("A1:A100000").Value
For i = 1 To UBound(data, 1)
If Not IsEmpty(data(i, 1)) Then
data(i, 1) = LCase(data(i, 1))
End If
Next i
Range("A1:A100000").Value = data
End Sub
Alternatives to VBA for Lowercasing
If you do not need a macro, Excel has built-in functions and tools:
- LOWER function: In a worksheet, use
=LOWER(A1)to get lowercase text. This is the simplest non-VBA method. - Flash Fill: Type the lowercase version in a neighboring cell, then press Ctrl+E (Windows) or Cmd+E (Mac) to auto-fill the rest.
- Power Query: In Excel 2016 and later, you can use Power Query (Data > Get & Transform) to transform a column to lowercase. This is non-destructive and refreshes.
These alternatives are often safer because they do not require enabling macros, and they are reversible.
Frequently Asked Questions
Can I convert text to lowercase in Excel without VBA?
Yes. Use the LOWER worksheet function. For example, in cell B1, type =LOWER(A1) and copy down. This is the easiest and safest method.
How do I convert a whole column to lowercase permanently?
You can use a VBA macro like the one above, or use the LOWER function in a helper column, then copy and paste as values over the original data. The macro method is faster for large datasets.
Why does StrConv(vbProperCase) not lowercase the rest of the word?
Because vbProperCase only capitalizes the first letter of each word; it does not alter the case of the remaining letters. To get true title case, first apply LCase to the whole string, then apply StrConv with vbProperCase.
Does LCase work with accented characters like é or ü?
Yes, in most locales. For example, LCase("ÉCOLE") returns "école". However, behavior can vary with locale settings, especially for characters like Turkish ‘İ’.
Can I use LCase inside a worksheet formula?
No, LCase is a VBA function, not a worksheet function. In a worksheet, use LOWER instead.
How do I convert text to lowercase in Excel for Mac?
The VBA code is the same. In Excel for Mac, you can also use the LOWER function. The keyboard shortcut for Flash Fill is Cmd+E instead of Ctrl+E.
Related
- Excel Template for Project Budget: How to Build One
- Excel Home Budget Template: How to Build One
- House Construction Budget Excel Template: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Double Quotes in Strings: How to Escape a QuoteTo include a double quote inside a VBA string, type two double quotes in a row: VBA treats…
- Refresh Worksheet VBA: Methods, Examples, and FixesTo recalculate formulas on one worksheet with VBA, call that worksheet’s Calculate method. To refresh imported data, use…
- Excel VBA JSON: Parse, Read, and Write DataExcel VBA can work with JSON, but VBA has no built-in JSON parser. In desktop Excel, the usual…
- Excel VBA: How to Hide a SheetTo hide a worksheet with VBA, set its Visible property to xlSheetHidden, for example: ThisWorkbook.Worksheets("Report").Visible = xlSheetHidden. Use…
- Split String in VBA: Syntax and Practical ExamplesUse VBA’s built-in Split function to divide a string into a zero-based array: parts = Split(text, delimiter). For…
- Excel VBA AutoFilter: Select Visible Cells SafelyIn Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data range to get cells left visible by AutoFilter. Exclude…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.