Auto Capitalization of movie names.

S

shyy

Well-known Member

Joined

Nov 6, 2008

Messages

1,484

• Jan 16, 2014

• #2

Welcome to the Board!!

Might not be the best solution but for now this will help

=PROPER(A1)

 

Upvote 0

D

DaveBen

New Member

Joined

Oct 22, 2010

Messages

23

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Jan 16, 2014

• #3

Will that work for a whole column or just one cell?

 

Upvote 0

S

shyy

Well-known Member

Joined

Nov 6, 2008

Messages

1,484

• Jan 16, 2014

• #4

You would have to drag it down the column

 

Upvote 0

D

DaveBen

New Member

Joined

Oct 22, 2010

Messages

23

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Jan 16, 2014

• #5

I tried that and all I got was "0's" down the whole column. I than saved the file as a template, started a new worksheet from the template and it didn't work. The title I typed in stayed in lower case. The cell's I want this to work for are C2 to C63 and F2 to F63. Do I need to insert a new column for the formula to work? Any help would be greatly appreciated. Thanks in advance.

Dave.

 

Upvote 0

S

shyy

Well-known Member

Joined

Nov 6, 2008

Messages

1,484

• Jan 16, 2014

• #6

You would have to insert a new column. Note this will only capitalize the first letter of every word.

=PROPER(C2)
=PROPER(C3)
=PROPER(C4)
etc...

Excel 2010
	A	B
1	a new hope	A New Hope
2	empire strikes back	Empire Strikes Back
3	return of the jedi	Return Of The Jedi
Sheet11

Cell Formulas
Range	Formula
B1	=PROPER(A1)
B2	=PROPER(A2)
B3	=PROPER(A3)

 

Upvote 0

D

DaveBen

New Member

Joined

Oct 22, 2010

Messages

23

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Jan 16, 2014

• #7

Is there another way to do it without inserting another column(s)? I would like it so that the cell I enter the data in automatically does the proper case without having to add another column after it??

 

Upvote 0

S

shyy

Well-known Member

Joined

Nov 6, 2008

Messages

1,484

• Jan 16, 2014

• #8

You are going to need a VBA solution, hopefully someone can make one.

 

Upvote 0

D

DaveBen

New Member

Joined

Oct 22, 2010

Messages

23

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Jan 16, 2014

• #9

How would I make a VBA solution? Any help would be greatly appreciated. Thanks in advance.

Dave

 

Upvote 0

jeffreybrown

Well-known Member

Joined

Jul 28, 2004

Messages

5,152

• Jan 16, 2014

• #10

With this code, anytime you enter anything in A1:A11, the value you entered will be made into proper case just like using the formula shyy provided.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Target.Parent.Range("A1:A11")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = Application.WorksheetFunction.Proper(Target)
    Application.EnableEvents = False
End Sub

This code is placed in the VBE under the sheet code...

Where do I paste the code that I find on the internet

 

Last edited: Jan 16, 2014

Upvote 0