I need to capitalize every cell in excel with first letter as capital?any easy way to accomplish it?
Yes, use this macro. Remember to take a back up of the file first!
Sub uppercase()
For Each cell In Application.ActiveSheet.UsedRange
If (cell.Value <> "") Then
cell.Value = UCase(cell.Value) ' this will make the entire cell upper case
End If
Next
End Sub
To make the first letter of each cell upper case you'd use
cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, Len(cell.Value) - 1) 'This will make the first word in the cell upper case
To make it title case, use
Sub titleCase()
For Each cell In Application.ActiveSheet.UsedRange
If (cell.Value <> "") Then
cell.Value = TitleCase(cell.Value) ' this will make the entire cell upper case
End If
Next
End Sub
Function TitleCase(s) As String
a = Split(s, " ")
For i = 0 To UBound(a)
If (Trim(a(i)) <> "") Then
TitleCase = TitleCase & UCase(Left(a(i), 1)) & Right(a(i), Len(a(i)) - 1) & " "
End If
Next
TitleCase = Trim(TitleCase)
End Function
How do I add VBA in MS Office?
=proper(H1)
in a helper cell will giveUpload File With Irregular Format
there.