Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 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:

=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


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 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:
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


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!




Hello,

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?

Thanks

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 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?


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


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.


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


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


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!


Fellow Forum Members,

The code below converts a cell containing ALL CAPS text to Proper Case text in another cell:

=PROPER(A1)

How can the function above be modified so that it includes exceptions for words like:

"and"
"of"

These type of words need to stay as lower case in a PROPER case function. For example:

"Thelma and Louise"
NOT
"Thelma And Louise"


Any help will be greatly appreciated. Thanks.


Hi,

I came across the following code (which converts text to proper case):

Code:

Sub Proper_Case()
   ' Loop to cycle through each cell in the specified range.
   For Each x In Range("A:A,B:B,K:K")
      ' There is not a Proper function in Visual Basic for Applications.
      ' So, you must use the worksheet function in the following form:
      x.Value = Application.Proper(x.Value)
   Next
End Sub


It is taking a relatively long time to execute. Can someone give me a better one, please.

Thank you,
Gos-C


Hello again,

Due to my limited VBA knowledge and the lack responses to the thread below. I have decided to move in another direction.

http://www.mrexcel.com/forum/showthread.php?t=387504

The issue I am having is how do I limit the results using the =SEARCH() Function to only identify exact matches. For example: If I search for "SS" in a text string how do I limit the results to "SS" by itself and not words containing "SS' such as "COMPASS". The search criteria will be dynamic. I have 7 fields in which words or groups of words can be entered. I would like to be able to toggle the exact match functionality. There are 4 possible scenarios in with the searched word could be found. Reference A1 to find any occurrence, A1&" " for the exact match at the beginning of the string, " "&A1&" " for exact match in the middle of the string and " "&A1 for exact match at the end of the string. I am envisioning a completely formula based solution. If anyone would like to provide assistance on the thread above it would be greatly appreciated. However, this solution would be a separate effort.

Thank you in advance,

Joe


I am trying to create a function that will pull together 3 other functions into a single function....

Hows that for a mouthful, Im confused already

I am falling at the early hurdles can anyone help.

Here is what I have so far:
Code:

Function MegaFunction(Var1 As String, Var2 As String, Var3  As String, Var4 As String, Var5 As String, Var6 As String, Var7  As String, Var8 As String) As String
Dim Stock As String
Dim Orders As Long
Dim Cleanup As String

   
If Var1  "" Or Var2  "" Or Var3  "" Or Var4  "" Then _
    Stock = Stock + Var1 + Var2 + Var3 + Var4
        Call Neat(Stock)
        Call Tidy(Stock)
    Else Stock = Stock + Var5 + Var6 + Var7 + Var8
        Call Tidy(Stock)
    End If


My current issues a

1: When I call Neat I get message "Compile Error: ByRef argument type missmatch"

Neat starts like his
Code:

Public Function Neat(MyRange As Range) As String


2: Calling Tidy is OK but how do I get the result of the function passed back into the calling function?

I need the result of the called function to pass to the next function later on.


I am working with a spreadsheet which contains employee names and addresses. Everything was initially entered in Capital letters. I would like the easiest way to reformat my cells so that the text has only the first letter of each word capitalized.

I am aware of the PROPER function but am not using it correctly. Any assistance would be greatly appreciated.