How can i force capitalization of first letter of each word in Excel cell

Question

I'm working on a simple Excel sheet, where users enter their first name, sometimes they don't use capital letters, so I'm trying to make the name cells capitalize first letter automatically when they fill in their name.

I tried PROPER formula but this is useful between 2 cells / columns.

When a user Enter "jose gonzalez" in the name cell and hit enter, the content become "Jose Gonzalez".

@BigBen `When a user Enter "jose gonzalez" in the name cell and hit enter, the content become "Jose Gonzalez".` would require vba. A formula would need to be done in another cell from the one where the data was entered. — Scott Craner, Sep 26 '19 at 13:31
Yep @ScottCraner I agree I just was confirming whether OP can actually use VBA (like if it's not blocked by corporate policy). — BigBen, Sep 26 '19 at 13:34
Hi, yes i can use VBA formula. Thanks for the help. I need to apply the rule on a range of cells. D2:D1000 — Ludwig Arcache, Sep 26 '19 at 14:05
You can combine [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) and [THIS](https://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells/19986324#19986324) to get what you want ;) — Siddharth Rout, Sep 26 '19 at 14:38

SlappedProgrammer · Accepted Answer · 2019-09-26T13:43:49.190

4

Using the VBA editor (Alt + F11) in the Sheet module that you're working on, paste the following code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then
'more than one cell
Else
    If Target.Column = 5 Then
        If Len(Target) > 0 Then
            Application.EnableEvents = False
            Target = Application.WorksheetFunction.Proper(Target)
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

Mine is using column E which is 5 so you would need to adjust accordingly.

edited Sep 26 '19 at 13:43

answered Sep 26 '19 at 13:36

SlappedProgrammer

194
1
11

1

You need to check if `Target` is only one cell. Otherwise this will fail. – BigBen Sep 26 '19 at 13:38
"When a user Enter "jose gonzalez" in the name cell" – SlappedProgrammer Sep 26 '19 at 13:40
Try changing multiple cells simultaneously in column E and see what happens. `Len(Target)` will fail. (as will `Target = Application...`). – BigBen Sep 26 '19 at 13:41
1

see updated answer. Also if you want it just for one cell, then you can change `target.column = 5` to `target.address = "E5"` – SlappedProgrammer Sep 26 '19 at 13:43
You could loop through target in the (unlikely) event that it's multiple cells. – SJR Sep 26 '19 at 13:44
Upvoted. I prefer [`Range.CountLarge`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.countlarge) to `Range.Count`, but yep you've fixed the issue. – BigBen Sep 26 '19 at 13:46
Im trying to apply the rule on a range of cells, D2:D1000 – Ludwig Arcache Sep 26 '19 at 14:03
@LudwigArcache change `If Target.Column = 5 Then` to `If Not Intersect(Range("D2:D1000"), Target) Is Nothing Then` – Scott Craner Sep 26 '19 at 14:11
@ScottCraner thank :) – Ludwig Arcache Sep 26 '19 at 14:14

score 0 · Answer 2 · answered Sep 26 '19 at 13:32

0

The formula you're looking for is:

=PROPER(yourcell)

So, if you have written josE GonzalEz in A1, typing =PROPER(A1) will return a nice "Jose Gonzalez" in your cell :)

answered Sep 26 '19 at 13:32

Pau

61
5

1

The OP stated `I tried PROPER formula but this is useful between 2 cells / columns.` The OP wants it to be done in the same cell as the data is entered. – Scott Craner Sep 26 '19 at 13:34
Yes, PROPER is useful if you want to transform data between 2 cells – Ludwig Arcache Sep 26 '19 at 14:01

How can i force capitalization of first letter of each word in Excel cell

2 Answers2