Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

In Vba, Check If First Letter Is Capitalized (urgent)

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

In VBA, I need to check if the first letter of a character string in capitalized.
(I do NOT want to change its case, though)

var1 = "task"
Is the t capitalized?

I found the string function "exact()" and the information on it indicated that the string funciton is case-sensitive, so it can be used to test if the ltter cases are identical to the proper-case version of the string. Their example was checking cell A1.

I tried:
If exact(var1,PROPER(var1)) ' this did not work

dim checkfirstchar as string
Checkfirstchar = exact(A1,Proper(A1)) " (function not defined)

dim checkfirstchar as string
If Checkfirstchar(exact(A1,Proper(A1))) then " (function not defined)
msgbox "if true, then capitalized)
end if

Your help would be so appreciated. I need this info urgently.

View Answers     

Similar Excel Tutorials

Capitalize First Letter of Every Word in a Cell - PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell.  This allows you to prepare ...
Make Column Headings Numbers instead of Letters - Make R1C1 Style References in Excel 2007
In Microsoft Excel you reference columns as letters by default - A1, B3, C5, etc. But you can also reference the co ...
Make All Text Upper Case or Lower Case in Excel
How to quickly change all text to upper case or lower case in Excel.  This allows you to change the case of text wi ...
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...

Helpful Excel Macros

Capitalize the First Letter of Every Word in a Cell
- This macro will make the text of any selected cell in excel proper case. This means that the first letter of any word in
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

Similar Topics

In Excel 2003, I am referencing a text string and would like to return the text string with only the first letter capitalized. The PROPER function does not do this, as it capitalizes the first letter of every word. The formula below does however perform this, but I would like to write it as a User-Defined Function, similar to the Proper function, so it can be applied to multiple different workbooks:


Is it even possible to write a User-Defined Function to complete this seemingly simple task and, if so, how do I do it?



I have data being stored in an array that is all capitalized. When I export the data onto my sheet, the capitalized data is found in cells (A1:A10). I don't want to use the proper function; instead I would like to build the proper function into my macro somehow. Are there any thoughts on how to do this? Thanks.

Okay, I've had a lot of good help on the topic of changing certain entries to proper case. The current script I have with me is:


Sub Test()
Dim cl As Range
For Each cl In Selection
cl = UCase(Left(cl, 1)) & LCase(Right(cl, Len(cl) - 1))
Next cl
End Sub

It basically turns all the current selection to sentence case. Sentence case is the first letter of the first word capitalized, then the rest uncapitalized.

My question is if you can make exceptions where the word Bible or God, for example, would be capitalized and the rest normal.


I just bought a mailing list for my business and need to send a formal letter to each individual - unfortunately the letter is in all caps which doesn't make for a professional looking letter. Can anyone tell me how to format an entire column into proper case (each first letter capitalized and subsequent letters lower case) without having to put a separate formula into each cell? Thanks so much - this is driving me crazy -

I have a worksheet w/ all caps & I need to change it so that the first letter of each word is capitalized. I used Excel Help and it said to use the PROPER function, except I can't seem to make that work. Any help? Thanks.

I am working with an existing spreadsheet. All data was entered in capital letters. I would like to change this so that some ranges contain all caps, some all lower case and some are "Proper" with only the first letter capitalized.

What is the quickest way to make these changes?

Hi there;

I am trying to do a very basic comparison between two text strings to see if they are exact or not. Please reference my setup:

1st text string:
A2: =LOWER(A1) which produced alberta education (lower case)
A3: pasted A2 as special paste "values"

2nd text string:
A5: =LOWER(A1) which produced alberta education (lower case)
A6: pasted A5 as special paste "values"
A7:=EXACT(A3,A6) which gave me a "false" response

Some other background, the original information was all capitalized, so I used the =LOWER function to convert to lowercase (I understand the =LOWER formula is case sensitive). I than did a "paste special" as a "value" from the =LOWER formula. Both A3 and A6 cells are formated as "text". Help, this should be pretty straight forward but I am struggling. Any suggestions out there?

Thanks for listening

This one should be a piece of cake for you guys, but I can't for the life of me find the code for this.

I have a bunch of words (artists, to be exact) in column A.

Words that already have every single letter capitalized I want moved to Sheet 2.

Because if the artists are already in proper case, I can assume it is listed right. If I run proper case formatting on all the artists in column A, it actually breaks some all CAPS artists that are listed properly. So I want to move ALL Caps to a different sheet so I can work with them individually.

Thanks in advance!

Hi eveyone!

When using the PROPER function, it capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter, and converts all other letters to lowercase.

However, if A1 contains the text "2-cent's worth"; then =PROPER(A1) will return the following result: "2-Cent'S Worth".

Is there a way to prevent the PROPER function from capitalizing the first letter following the apostrophe?

Thank you!


I have sample string: Cat jumped over lazy Dog

I only want to capitalize: LAZY

so it should be: Cat jumped over Lazy Dog

Proper capitalizes first letter of every word, just want one word capitalizaed.

Also, if I want to use an and function to capitlize multiple words, so it should be: Cat Jumped over Lazy Dog

where just Jumped and Lazy are capitalized.

How to?


I now there is a formula in excel that copies a cell & converts the test to "Proper" grammer, 1st letter capitalized all others are small.

I can manually correct the list but how can I insure that when text is input to a cell this rule is followed ?

The function PROPER() works well for changing single cells to the proper
case. What I need is to convert the entire spreadsheet, or specific columns
or rows, to the proper case.
That is, if column C has some or all names in a variety of cases, I'd like
to change that to column C with all proper. Sort of a PROPER(Column C).
Same for row 12 to be PROPER(Row 12).
I'm getting data that is mainly uppercase which includes the state ID, such
as TX for Texas, etc. I don't want this to be made proper. But the names,
cities, addresses, and some descriptions in the data, I'd like to convert to
proper format.
Does there exist a utility or a way to structure the PROPER function so I
don't have to do each cell?

Hi all,

Pl suggest how to convert a given text string to Proper Case by VB code.
VB Code of Upper & Lower Case exsist but couldn't trace for the Proper Case, as we have it in Normal Excel Formula bar.

Thanks & Regards,

Vijay Anand.R

I've done a search all over the web and considering I pick up Excel once every 6 months for 5 minutes, I can't find a simple solution for my problem.

I believe that what I am trying to do is change some capitalized text to PROPER (or what the rest of the World outside MSoft calls 'Title Case').

One would think that it is as simple as going to a menu and picking 'Proper Case' like it is in other applications but it seems I have to jump through hoops and create macros (which are like the black arts to me and a complete mystery).

Someone please tell me I am completely wrong and there is a button somewhere.

Oh, and just to make your day even better.....I'm on a mac! HA!

I have a column of text; some cells have all upper case letters. I want all cells to have First letter only upper case, the rest in lower case.
I tried the PROPER function but am doing it wrong. I highlight the entire column, select PROPER, put the cells locations in the blank field (e.g., D2:D175), hit OK, nothing, except first cell now says PROPER. Please help.

I'm trying to figure out if there is any way of performing a logical check (possibly by way of a custom function?) to ascertain whether a cell contains a text string (no numbers) and is all in upper case (or lower case, or Proper Case).

The reason for this is that I receive approx 10,000 rows of data each month French customers who - for some strange reason - seem unable to tell the difference between a "First Name" and "Surname" field header (in French obviously!)

While most people do complete their data correctly, there is a large proportion where I can tell that the surname has been typed in the First Name field and vice versa. I can tell this because in France it is common practice to type your first name in proper case and your surname/family name in upper case.

What I therefore need is some way of identifying rows where data in Column A (First Name) is in upper case AND where the corresponding data in Column B (Surname) is in lower case or proper case.

Once identified, I can swap the values between columns.

Is there a relatively simple way of creating a logical check (to return TRUE or FALSE) on whether a cell's contents are in upper case, lower case or proper case?

Using Option Explicit
Looking for 2 things:

Make this into 1 line
Dim var1 as string
Dim var2 as string
Dim var3 as string
Thought this would work but must be off on the syntax
Dim var1 var2 var3 as string

Make this 1 line
Dim var1 as string



I dont know, if there is a solution to my problem

I have a long string ( a file path) which is contactentiated (via the ampersand) with some as strings (which should vary). This works perfect if i initialize the string in the vba code, but not at all when i try to initialize the long string via a Cell Range from the excel sheet

Below you find a simplified version of problem

Var2 does ignore the ampersand for contactenation, while Var does not. What can i do to make Var1 recognize the ampersand?


Sub Stringcontact()

Dim var1 As String
Dim var2 As String

Dim datum_string As String
datum_string = "01/01/2010"

var1 = "Datum" & datum_string
var2 = Range("A1") 'Cell A1 is: Datum" & datum_string

MsgBox ("Var1: " & var1 & vbNewLine & "Var2: " & var2)

End Sub

Thank you

I have a list of towns and some are in capitalized type. I would like to know if there is a way to change all words except the first letter from upper case to lower case.


I am trying to get a function that will convert a string to a unique number. The function can return an error if more than 1 cell is selected and also if characters in the string are not letters, and does not need to be case sensitive.

The function would work like this:


In that case it would be parsing through the string & replacing each letter with the # of that letter in the alphabet (A=01, etc.)

Ideally there is some kind of function in VBA to convert a letter to its ASCII number or something, which probably would mean it could support symbols & spaces but, again, thats not necessary.



I want to change my Uppercase text to proper.

I found and used the Proper() function. What I am wondering about is when I have a Name such as MCDONALD or O'NEIL. What can I use in cases such as this to convert the text properly?



Dim Var1 As String, Var2 As String
Var1 = InputBox("Variable 1?")
Var2 = InputBox("Variable 2?")

If I have two string variables defined as:

sFirst = "ActiveCell.Value = Val(Var1)"
sSecond = "ActiveCell.Offset(, 1).Value = Val(Var2)"

The value of ActiveCell and ActiveCell.Offset matches that of Var1 and Var2

I wonder if someone could advise what I am doing wrong when the following will not work? What is it I am doing wrong? I get "run time error '13': Type Mismatch". Thanks so much.

If (sFirst) & " " & "And" & " " & (sSecond) Then
MsgBox "works"
End If

I have a variant/string for Var1 which is "20090831"

I need to have Var2 be "08-31-2009"

I've tried using CDate(Var1) and Format(Var1, "MM-DD-YYYY") but receive different run-time errors. I can use left-right-mid and piece everything back together to get what I need, but I know there has to be an easier way. How do get around this?


I have a worksheet filled with names all in Capital letters all in one cell something like this:

I would like to separate the name into first and last and have the both the first and last formatted so the first letter of both the first name and the last name are capitalized and the rest of the name is in lower case. First name in one column and last name in another column and middle initial, if included, to be omitted.

Can someone give some advice?


Hey all, I'm doing a project that requires me to change ALL CAPS to proper case as part of it. Basically, I just want a macro that when I select a cell and press the macro button, would change it from all caps to proper case.

I am no expert at VBA, so it would be nice if you could fix the code I tried to piece together.


Sub Macro3()
' Macro3 Macro
    Dim Textie As String
    Set Textie = Selection
    Application.Proper (Textie)
End Sub

Do I have something wrong? When I try to run it, the debugger jumps to Set Textie = Selection and brings up a message box saying that an object is required.