If you’d like to capitalize all letters in a cell, it’s easy – just use the following formula (assuming cell “A1″ has the text you want to capitalize).

`=PROPER(A1)`

But what if you just want to capitalize the first word in the cell “A1″? For example, you have the following text in a cell “A1″ and you’d like to capitalize just the first letter:

“How to Get a High School Diploma in 1 Day”

To capitalize “How” in cell “A1″, use one of the following formula:

`=REPLACE(A1,1,1,UPPER(LEFT(A1,1)))`

or

`=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))`

The result:

“How to get a high school diploma in 1 day”

Not working for you?

Remember to replace “A1″ with the cell that contains the string of words you want to parse out.

## 46 Comments

An outstanding share! I have just forwarded this onto a co-worker who has been conducting a little research on this.

And he in fact bought me lunch because I found it for him…

lol. So let me reword this…. Thank YOU for the meal!!

But yeah, thanx for spending time to talk about this subject here

on your web site.

Hahaha! Glad to hear you were able to help your colleague and got a free lunch too

Hi Karen,

How do I just capitalize the first word in a cell?

life is warfare, and the Stoic achieves serenity by arming himself to face whatever may be inflicted on him by the vicissitudes of events, the turning Wheel of Fortune.

I’d just like to capitalize ‘Life…

Thank you,

Walter

Hi Walter, you can use this formula:

`=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))`

Hi,

I typed several names in Column A1:A20 and inserted the formula, =proper(A1) in cell B1. It worked. But when I tried copying this formula (by draging the cursor) in cell B2:B20 then it copied the formula but the value was still of B1.

Hi Trip! It sounds like your worksheet isn’t calculating automatically. You can request a full calculation of all formula by pressing Ctrl-Alt-F9.

Hi,

I typed several names in Column A1:A20 and inserted the formula, =proper(A1) in cell B1. It worked. But when I tried copying this formula (by draging the cursor) in cell B2:B20 then it copied the formula in those cells but the value was still of B1. Please advise.

Hi Trip! It sounds like your worksheet isn’t calculating automatically. You can request a full calculation of all formula by pressing Ctrl-Alt-F9.

Hello all I just have a column B2 which is labelled SEX and I enter M or F but it is all lower txt, I have to manually change it upper case.

how do I change it so that it automatically goes to upper case.

many thanks if you can help

Hi Richard! I understand that you have the character “m” or “f” (lowercase) in a cell and you want to convert these into either “M” or “F” (uppercase). That’s an easy one! You can easily use either the “PROPER” or “UPPER” function.

Assuming cell “A1″ has the text you want to capitalize, you can use one of the following:

`=PROPER(A1)`

`=UPPER(A1)`

I think we should prefer this formula in this case:

“Military land Vehicles Moving on the street”

to

“Military land vehicles moving on the street”

Formula:

=CONCATENATE(UPPER(LEFT(A3,1)),LOWER(RIGHT(A3,LEN(A3)-1)))

Enjoy

Hi Raaj! That’s right. You should choose the second formula if you only want to capitalize the first word in the string.

Just made a big job disappear with this formula, brilliant

Hi, thx. I have some capitalized phrases and some all caps, so, I used this:

=CONCAT(UPPER(LEFT(A1;1));RIGHT(LOWER(A1);LEN(A1)-1))

Nice.

Thank you! You just made my day!

Hi Arnaldur! I am so happy I was able to help

Hi,

I have used “=PROPER()” formula, but it doesnt help me. I dont want my employees to have to copy and paste corrected text. I need Excel to correct the same text my employees fill in. And i need it to be corrected in the same cell as it was entered.

For example: Cell A1 – text entered: i need a car ->hit enter-> Cell A1 corrected: I Need A Car

Hi Dusan! Unfortunately, you can’t achieve that using the =PROPER() formula. If you want programmatically capitalize all the text in one go, you can use a macro. I have a tutorial here: http://howtouseexcel.net/how-to-capitalize-all-words-using-an-excel-macro-instead-of-the-proper-function However, you still have to run the macro. It doesn’t automatically update the cell immediately after you enter the data.

Thank you

In the end I used this:

Private Sub Worksheet_Change(ByVal Target As Range)

””””””””””””””””””””””

‘Forces text to Proper case for the range A1:B20

””””””””””””””””””””””

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

On Error Resume Next

If Not Intersect(Target, Range(“A1:B20″)) Is Nothing Then

Application.EnableEvents = False

Target = StrConv(Target, vbProperCase)

Application.EnableEvents = True

End If

On Error GoTo 0

End Sub

It was found here: http://www.ozgrid.com/VBA/force-case-text.htm

Hi Dusan! Thanks for sharing this! I’m glad you found a solution that worked!

thank , done

You’re welcome Prabhat!

Try This

=REPLACE((LOWER(A1)),1,1,(LEFT(A1,1)))

Too many ( & ) in this example and does’t work as it just replaces the 1st character with exactly the same character.

One working example would be…

=REPLACE(A1,1,1,UPPER(LEFT(A1,1)))

Hi Alan, I like your alternative formula. It is indeed much simpler than what I had originally proposed. I updated my post above and added this as an alternative. Thank you for your suggestion! I appreciate it.

Thank you for taking the time to share how to capitalize letters in excel. Thank you.

You’re welcome! Glad to hear it was helpful for you

I have a problem which i can’t seem to figure out, would appreciate it if you could come up with a solution.

A cell contains the last name of customers (uppercase) and in the same cell the last name is followed by the address.

e.g ” SMITH A, 36 James street”

I want to know how to get the last name of a customer into another cell without having to manually do it everyday for 10.000 customers.

The surname changes as you scroll down and each surname is only written once, so if there was another SMITH there would be a cell bellow which will one contain the first INITIAL of the first name e.g ” J, 178 Brooklyn Street.

Hi Bilal! To make sure I understand your problem correctly, do you mean that cell A1 has “SMITH A, 36 James street” and cell A2 has “J, 178 Brooklyn Street” and you want to extract the surname “SMITH” for both entries? If so, you can first extract the text before the comma using this tutorial here: http://howtouseexcel.net/how-to-extract-words-before-and-after-a-comma After that, you can extract the first word using this tutorial here: http://howtouseexcel.net/how-to-extract-the-first-word-in-a-cell-in-excel Hope this helps!

this just only for a cell.

how can we make the whole column do the same thing?

Hi Max, you can easily just drag down the formula using Excel’s auto-fill function. When you move the cursor to the bottom right hand corner of the cell, you’ll see a small square. When you double click on it, your formula automatically gets copied down to the last cell.

Hi there,

I get people to enter data for me and they don’t always use capitals. Is there any way I can make it automatically capitalize certain columns when they are typing?

Hi Melody, I understand that you want the first letter of the cell to always be capitalized as they are typing. Do you want to capitalize just the first letter of the cell? Or the first letters of all the words in the cell? Here’s what you can do:

1. Ask your employees to enter data under column A.

2. In cell B1, enter either “=PROPER(A1)” or “=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))” depending on whether you want to capitalize just the first letter of the cell Or the first letters of all the words in the cell.

3. Drag the formula in B1 all the way down the Excel sheet so that it gets copied all the way down.

After your employees submit the Excel sheet to you, just copy and paste column B as values and then delete column A. Hope this helps!

Can anyone help I have used the formula to capatalise first letters in cells.

=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))

However I need to also capitalise the first letters after full stops i.e.

“I went to the park. it was a lovely day.” to actually read

“I went to the park. It was a lovely day.”

Is there a formula for this.

Peter, I don’t have one formula in mind. But I have some thoughts on how you can get this done. Here are the steps:

Step 1. Separate the cell into several cells, with one cell containing one sentence each. You can do this by using the “Text to columns” feature using the ff. steps:

– Select the range of data that you want to convert.

– On the Data tab, in the Data Tools group, click Text to Columns.

– In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.

– In Step 2, select the Other check box, enter period “.” in the blank next to “Other”, and then clear the other check boxes under Delimiters.

– The Data preview box shows each sentence in one column.

– Click Finish.

Step 2. Use the formula in this tutorial to capitalize the first letters.

Step 3. Combine the cells again into one cell by using the CONCATENATE function.

My suggestion seems a little convoluted, so I apologize. But this is the only thing I can think of right now. If you find a better solution, let me know!

Hi Karen, what if I have a list of cells with various phrases in them and only some of the first words are capitalized, and I want the end result to be that, for every cell, each phrase or sentence should have only the first letter of the first word capitalized, that is, to make each cell grammatically correct in terms for capitalization.

CELL A1: ‘the warehouse is at 85 percent capacity’ turns into ‘The warehouse is at 85 percent capacity’

CELL A2: ‘The Warehouse is at 90 Percent Capacity’ into ‘The warehouse is at 90 percent capacity’

Hi Drew, did you try this formula?

=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))

This should hopefully work.

Nice “trick”. Thanks a lot.

Happy to hear that you found this “trick” helpful!

My friend, i’ve tried this formulas but it doesnt work for me

I am sorry to hear that the formula did not work for you. Are you sure that you have the correct cell reference in your formula? For example, the original text must be in cell A1 for the exact formulas above to work.

Thanks! Was literally able to cut-paste with success. Saved me some time learning excel macros!

that formula is magic ! There are so many complicated solutions on the net & this “=CONCATENATE(UPPER(LEFT(F2,1)),RIGHT(F2,LEN(F2)-1))” just worked beautifully, thank you !

Glad you found it helpful

How to capitalize the first letters in all the words in a cell in Excel? Per example how to make: james hasdes —-> James Hasdes

Stephanie, that’s very easy. Just use the “PROPER” formula. For example, if you want to capitalize the first letters of all the words in cell A1, just type “=PROPER(A1)”. Good luck!