Wednesday, May 6, 2020

VBA: A little function to Capitalize and Split a String

Here's a subroutine for converting a range of strings into a space separated Capitalization (i.e.) "FOO_BAR" becomes "Foo Bar"


Sub Capitalization_Split()
'Declaring variables
Dim Cell As Range
Dim Source As Range
Dim Capitalized As String
Dim Humps As Variant
Dim Hump As String
Dim i As Long
'Initializing source range
Set Source = Application.Selection
'Looping through each cell in the source range
For Each Cell In Source
    Capitalized = ""
    Hump = Cell.Value
    Humps = Split(Hump, "_")
    For i = LBound(Humps, 1) To UBound(Humps, 1)
        Hump = UCase(Left(Humps(i), 1)) & LCase(Right(Humps(i), Len(Humps(i)) - 1))
        Capitalized = Capitalized & Hump & " "
    Next
    Cell.Value = Capitalized 

Next
End Sub

No comments:

Post a Comment