Code Done Right!

Cell text cleaning

This procedure is changing the contents of the cell to be displayed in sentence case, additionally it removes any leading and trailing spaces as well as any multiple spaces between words. It is useful if you import/paste data to MS Excel that needs a client facing look. Nobody likes ALL CAPS look. Nobody.

Sub CellTextCleaning()
    Dim CellValue As String
    Dim CellLen As Integer
    Dim CellRest As String

    Selection.NumberFormat = "@"            'Changes the cell type to text
    ActiveCell.Value = Application.WorksheetFunction.Trim(ActiveCell.Value) 'Removes any leading, trailing and multiple spaces
    ActiveCell.Value = LCase(ActiveCell.Value)  'Sets lowercase for the whole string
    
    CellValue = ActiveCell.Value            'Loads active cell's contents to CellValue
    CellLen = Len(CellValue)                'Counts number of characters
    CellRest = Mid(CellValue, 2, CellLen)   'Loads active cell's contents to CellRest starting from 2nd char

    CellValue = Mid(CellValue, 1, 1)        'Loads active cell's first character
    CellValue = UCase(CellValue)            'Capitalises the string

    ActiveCell.Value = CellValue & CellRest 'Joins capitalised string CellValue with CellRest to produce the end result
End Sub

Above video depicts the use of the procedure