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?

  • 1
    The "Proper" function can do what you want. E.g. Putting =proper(H1) in a helper cell will give Upload File With Irregular Format there. – K. Rmth Jul 7 '15 at 10:57
  • Do you want just the first letter of the cell in Upper case, or do you want every sentance to be Title Case? en.wikipedia.org/wiki/Letter_case – Dave Jul 7 '15 at 12:24
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?

  • What if the cell contains more than one word?? – Gary's Student Jul 7 '15 at 11:59
  • 1
    @Gary'sStudent, I'm not sure this is what the OP wants. The OP didn't ask to Title case it. Currently, it will convert my words to My words ... hmmm, it isn't totally clear what the OP wants – Dave Jul 7 '15 at 12:23
  • @Dave let me update the question and get back to u friend – BlueBerry - Vignesh4303 Jul 7 '15 at 12:30
  • Post updated with example of making entire cell upper case @BlueBerry-vignesh4303 – Dave Jul 7 '15 at 12:31
  • updated question friend – BlueBerry - Vignesh4303 Jul 7 '15 at 12:38
6

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))

  • Sorry, how does this work? I mean, if I have a value in A1, and want to keep that value in A1, how can I use a worksheet formula? Or would I need to copy everything else where, and then copy it back (which is easy enough) – Dave Jul 7 '15 at 13:06
  • If you want to keep it in the same cell then you are correct. You must create a second column with the formulas and then copy / paste values over the original data. That's the only drawback against VBA: It can't edit the data in-place. – Engineer Toast Jul 7 '15 at 13:18
  • ... This is probably a better solution than mine as it is probably more accessible +1 – Dave Jul 7 '15 at 13:21
  • Agree, with proper being the better choice now, unless the need is only for the first word. +1 – Raystafarian Jul 16 '15 at 11:35
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
  • updated question with sample stuff kindly have a look at it friend – BlueBerry - Vignesh4303 Jul 7 '15 at 12:39
  • 2
    Dave already gave you that solution, then you said you wanted the entire cell in caps. Please make up your mind. – CharlieRB Jul 7 '15 at 12:41
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.

-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.

  • The question is NOT about LibreOffice. – RamonRobben Mar 3 '17 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.