2

Recently i came across excel with cells filled with small cases,Is it there any easy way or macro to capitalize each word of the excel?

For E.g Consider the below image :

Before conversion

After conversion it should look like as below :

After conversion

I need to capitalize every cell[Entire cell] in excel with first letter as capital?any easy way to accomplish it?

BlueBerry - Vignesh4303
  • 8,280

6 Answers6

7

There's no need to use VBA to change any cases. There are certainly VBA functions that do this as shown by other answers but, unless you're already writing something in VBA, this is overkill.

The following formulas will convert cases for you.

=UPPER(A1) converts all letters to uppercase
=LOWER(A1) converts all letters to lowercase
=PROPER(A1) converts the first letter of each word to uppercase and every other letter to lowercase
(A "word" is a consecutive string of letters. Any non-letter is considered the end of a word.)

Here are examples of the results:

Input:    Aaa bbb-ccc/ddd=eee9fff"ggg\hhh{iii(jjj
Upper:  AAA BBB-CCC/DDD=EEE9FFF"GGG\HHH{III(JJJ
Lower:  aaa bbb-ccc/ddd=eee9fff"ggg\hhh{iii(jjj
Proper: Aaa Bbb-Ccc/Ddd=Eee9Fff"Ggg\Hhh{Iii(Jjj

If you want to capitalize only the first letter of a cell, combine UPPER with some other functions:
=UPPER(LEFT(A1)) & MID(A1,2,LEN(A1))

Engineer Toast
  • 5,218
2

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?

Community
  • 1
Dave
  • 25,513
2

Here is a simple macro to convert text in a range to all CAPS. Change the range on the 3rd line to the range you want to convert.

Sub Uppercase()
   ' Loop to cycle through each cell in the specified range.
   For Each x In Range("A1:A25")
      ' Change the text in the range to uppercase letters.
      x.Value = UCase(x.Value)
   Next
End Sub
CharlieRB
  • 23,021
  • 6
  • 60
  • 107
0

Simplest is to make your own function (New York, hence it was the first capital of the USA....)

Function NewYork(InputText As String)

NewYork = UCase(Left(InputText , 1)) & Right(InputText , Len(InputText ) - 1)

End Function

Gabor Szilagyi
  • 1
0

You can also copy necessary data into a Microsoft Word document and change the letter cases using the "Aa" button in the Home > Font tab. Then just copy and paste back into Excel.

Chelsea Owen
  • 11
-2

Use LibreOffice! You can open, change and save Excel files. 1. Select the cells you want to change 2. Point at Format > Text > Change Case > Capitalize Every Word Simples.

Andrew Rowland
  • 1