A personal blog of Christopher Wigginton; a nerd by nature, programming polyglot, cooking enthusiast and fermentation practitioner.
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"
'Declaring variablesDim Cell As Range
Dim Source As Range
Dim Capitalized AsStringDim Humps AsVariantDim Hump AsStringDim i AsLong'Initializing source rangeSet Source = Application.Selection
'Looping through each cell in the source rangeForEach Cell In Source
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