VBA - Capitalise range of cells before the last period

Question

Hi I’m new to VBA and macro stuff, and wanted to know how best to go about the following:

I have some code that currently capitalises a range of cells. I want to change this however to only capitalise the contents before the last period in each cell.

So if for example I have:

Column A	Column B
TRUE	text1.new.txt
Cell 3	text2.new.txt

What can I add to the following to ensure that when run, only the text before the last period is capitalised?

Sub UpdateNames()
    If Range("A1") = True Then
    Range("B1:B100") = [INDEX(UPPER(B1:B100),)]
    End If
End Sub

Thanks!

Answer 1

Take a look into the InStrRev function. It can be used to find the position of the last period to split the string by that:

Sub UpdateNames()
    If Range("A1") = True Then
        ' get left part by last period position
        lft = Left(cell.Value, InStrRev(cell.Value, "."))
        ' get right part by difference between whole string length and left length
        rght = Right(cell.Value, Len(cell.Value) - Len(lft))
        ' put both together
        cell.Value UCase(lft) & rght
    End If
End Sub

Alternatively you can use the Split and Join functions. This might not be the most elegant and certainly not the fastest solution as it loops over each substing but it still does the trick:

Sub UpdateNames()
    If Range("A1") = True Then
        ' get all elements of the string into an array
        arr = Split(cell.Value, ".")
        ' capitalize every substring except the last one
        For i = 0 To UBound(arr) - 1
            arr(i) = UCase(arr(i))
        Next i
        ' join the substrings back together 
        cell.Value Join(arr, ".")
    End If
End Sub