Ever received a spreadsheet and all the data on it is in the wrong case? You might received a dodgy dump of data from your website or database, or maybe the user-inputted data is poorly or inconsistently formatted. Here I’ll guide you through how to do Excel VBA Uppercase, lowercase and capitalising the first letter of a string. All these tasks are invaluable when you have to manipulate your data using VBA.
Excel VBA Uppercase
First, converting strings to upper case through Excel VBA: you need the Ucase function. Let’s say you want to loop through column A, replacing the current cell with an upper case version:
For i = 1 to cells(Rows.Count, 1) Cells(i,1) = Ucase(Cells(i,1)) Next i
And that’s how you do Excel VBA Uppercase.
Excel VBA Lowercase
Lower case couldn’t be simpler: the Lcase function. Here’s an example of converting a variable to lower case:
Dim empStatus As String empStatus = "CONTRACT" empStatus = Lcase(empStatus) MsgBox empStatus
How do I capitalise the first letter in a cell (or string) ?
Excel VBA does not have a native way to do this; you could use some clumsy string manipulation methods but let’s lean on the Excel functionality: the Excel Proper function. You can summon any function from Excel using the Application.WorksheetFunction method.
Here’s an example, capitalising the first letter of every cell in column C:
For i = 1 to cells(Rows.Count, 3) Cells(i,3) = Application.WorksheetFunction.Proper(Cells(i,3)) Next i
Three quick ways to manipulate text strings using Excel VBA,