Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


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)

e.g.,
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.
=EXACT(A1,Proper(A1))

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

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

Tried
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 Video Tutorials

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:

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

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

Thanks,
Chris


Hello,

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:

Code:

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.

Thanks!


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.


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 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:
A1: ALBERTA EDUCATION
A2: =LOWER(A1) which produced alberta education (lower case)
A3: pasted A2 as special paste "values"

2nd text string:
A4: ALBERTA EDUCATION
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
Mark


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 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 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?
TIA,
Tom



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?


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.


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
var1="hello"

anyone?


Hi,

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?

Code:

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


Hi,

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:

=StringToNumber("Hello")
=0803121215

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.

Thanks,

Chris


Can I format data as a result of data input, without getting
a circular reference?

I want to input a string into a cell, say "abcd", and have the first
letter automatically capitalized, as "Abcd". In another case, I want
the entire input string to be all capitalized, so that "abcd" would
become "ABCD".

I can't put something like =UPPER(A1) into cell A1 without a circular reference.

Thanks,
Dave


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?

Thanks,

Bob


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 worksheet filled with names all in Capital letters all in one cell something like this:
CHRISTOPHER J OLSON

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?

Thanks
Alan


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?

Thanks


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.

Code:

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.

Thanks!