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