Excel Training VBA Training

Excel VBA Uppercase, Lowercase and more

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.

wrong kind of case. we want excel vba uppercase
Not this kind of case! (image copyright iconarchive)

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,

By Richard Bailey

I love what I do; I get to work with an outstanding team to help hundreds of people with their challenges. I’ve learnt a lot from the teams I’ve worked with, no matter the size or industry we all have challenges to overcome, difficult customers, creating a budget or keeping a project on track.