Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I would like the first letter in an Excel cell that I type to be capitalized always.

Posted on 2016-11-03
12
Medium Priority
?
44 Views
Last Modified: 2016-11-28
I would like the first letter that I type in an Excel spreadsheet to be capitalized, always.
Either for the entire excel sheet or specific columns or specific cells.
Is this possible?
Preferably if this could be done automatically without VBS or a Macro that would be good, otherwise those are also options.
Thanks in advance for your assistance.
0
Comment
Question by:100questions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
12 Comments
 
LVL 8

Expert Comment

by:Koen
ID: 41872179
I don't think it is possible without coding it...
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41872193
As far as I know there is no way of doing that without VBA, unlike in MS Word where it recognises the start of a sentence and capitalizes the first letter automatically.

You can convert entries to specific formats with some formulas:

=UPPER(Text)   will convert to all upper case
=LOWER(Text)   will convert to all lower case
=PROPER(Text)  will convert the first letter of each word to upper case and the rest to lower case.

Text can be a cell reference whose contents are to be converted. There is a limit to PROPER that I am aware of, if the text includes an abbreviation which should be all capitals, eg USD or USA or UK, it would still convert it, ie Usd, Usa, Uk.

Thanks
Rob H
0
 
LVL 8

Expert Comment

by:Koen
ID: 41872224
the word functionality (capitalizing the first word) works exactly the same in Excel... (try typing a multisenctence paragraph in one cell (with interpunction))

However, Excel is not a text tool, it is a spreadsheet... Hence Excel does not think you'll write full sentences. Therefor it does not work on single words (nor on your first full sentence).

The only proper way I see, is writing a macro that that capitalizes the first letter (according to some rules you can set)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41872255
Right click the Sheet Tab --> View Code --> And paste the code given below into the opened code window --> Save your workbook as Macro-Enabled Workbook.
The below code will do what you want if you input anything in the column A:Z.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("A:Z")) Is Nothing Then
    Application.EnableEvents = False
    Target = StrConv(Target.Value, vbProperCase)
    Application.EnableEvents = True
End If
End Sub

Open in new window

0
 
LVL 25

Expert Comment

by:Brian B
ID: 41872300
I don't know if this is useful to you, but if you only wanted to have a specific area or column like you described, you could do it using a formula:

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

Obviously you'd have the unformatted text in your spreadsheet as well, you would just have to leave it out of the print range, or hide it or something.
0
 

Author Comment

by:100questions
ID: 41872332
@ Subodh Tiwari (Neeraj)
Thanks very much.
Will the macro run automatically, or would it need to be triggered?
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41872732
This is a Change Event code and would be placed on Sheet Module (as per the instructions in my previous post). The code will be triggered automatically once you change or input any content in a cell in columns A:Z as per the code.
0
 
LVL 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points (awarded by participants)
ID: 41872737
Please find the attached (a blank workbook) with the code implemented.
To check the code, input a text in any cell in the range A:Z.
Capital-Letter.xlsm
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41872748
As always, please be aware that running any VBA clears your undo history.
0
 

Author Comment

by:100questions
ID: 41881181
@Subodh:
I tried this in a macro but it does not seem to work.
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41881554
Didn't it work in the file I attached as described?
The code in the attached was a sheet change event code. That means the code will be triggered automatically when you input a string in the range A:Z and it will be placed on the Sheet module not on the standard module like Module1 etc.
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41903917
The chosen answer clearly resolved the question.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question