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



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 ...
Convert Column Number to Letter Using a Formula in Excel
How to get a column letter from a number in Excel using a simple formula. This is an important thing to be able to ...
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 ...
Insert Check Mark in Excel - 3 Ways - Incl. VBA and UDF
This tutorial goes beyond other simple check-mark tutorials. Here, I'll show you 3 methods to insert a check-mark i ...

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







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!


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,

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


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.


How can I separate a column of cells containing a single text string in each[these are names formated as a string with no delimiter as in "FirstLast" with the first letter of first and last name capitalized and the rest lower case...] into two columns of text called Firstname and Lastname?

Thanks!


I have some basic code which converts data (address details) in a selected range to Proper Case, i.e. the first letter of each word is capitalised.

Code:

Sub Case_All_ProperCase()
For Each c In Selection
    c.Value = Application.Proper(c)
Next c
End Sub


Unfortunately, this also converts ordinal numbers such as 1st into 1St, 5th into 5Th and so on and converts anything following an apostrophe to a capital as well (e.g. "John's Court" becomes "John'S Court"

I can't simply do a find and replace on the St/Nd/Rd/Th elements of the text string as these may form part of a correctly capitalised address (e.g. I wouldn't want "High Street" to be changed back into "High street" or "The Boulevard" changed to "the Boulevard").

I had thought about replacing the specific ordinal number elements in their entirely(1St to 1st, 2Nd to 2nd and so on), but as this is address information, I could potentially have ANY number followed by the ordinal indicator, for example, my current data set has "50th Floor".

Is there any way of converting a range of text to Proper Case, but avoiding converting the text following a number or an apostrophe?


I'm trying to compare a very large amount of data with other data in a spreadsheet.

What I want to do is take a text string like and see if it matches anywhere else in the spreadsheet:
"Fireflies, Owl City"

I have tried using the exact function and a few others, but haven't been able to achieve the desired results.

What I would like is for the program to automatically state whether there is a match in the spreadsheet based on the text strings in another column or row.

For an example of what I mean, see the attached spreadsheet.

Please note that in this case this function will not work because it is not guaranteed to be in the same order.

Anyone have any suggestions?


I purchased a rather large list that is in all caps (in .txt format). Is
there a function that will allow Excel (or any other Office product) to
automatically change the names to proper case? I will need to save the
results back in .txt format to import into a contact management program.



I am playing around with creating a text encryption and decryption tool using mainly MS Excel and a little bit of MS Word. The version is 2003. I plan to use macros to prepare an unencrypted message, and for decoding an encrypted message.

My question at this point is: Is there a function which expands on "Code()". This function converts the 1st letter of a text string to its corresponding numerical value. Is there a way (either a function or some sort of workaround) to return the numerical value of the second letter of the text string (and the third, and the fourth, etc.).


If you are interested, here is the challenge I am working on (if not, you can skip this):

Create a workbook. List out all possible characters, numbered 1 to xxx. This would include uppercase, lowercase, numbers, and punctuation marks. Using the random number generator function, assign each letter a random number. Using the sort function, resort the data so that each letter now has a new randomly generated position in the alphabet. For example, If there are 150 different letter where A=1 a=2, B=3, b=4, etc. after they have been sorted, "A" has an equal chance of now being 1 - 150.

While this is a start, this type of code can easily be broken. So to make it more complicated, we can, from letter to letter. shift which key is used to determine what "A" equals. To creating more keys, we simply create a new worksheet page. and repeat the steps above. I would start with 10 pages of keys.

From there we break down the message letter by letter using the CODE() function. This is why i need to figure out how to look at the second and third position in a text string.

Once we have broken the message down to each letter, it is simply a matter of figuring out a way of choosing which key to use for each letter to encrypt the message. I was thinking of using a password, which only the sender and receiver know, which could help in choosing which key to use for which letter. Therefore, even if someone is able to obtain a copy of the workbook, the message would only make sense with the proper password.

For simplicity, lets say if the password is 5 letters long, use key #5 for the first letter, key #6 for the second letter, key #7 for the third letter, etc. If it is 10 letters long, start with key #10, etc. This way, the exact same message would be encoded (and decoded) differently depending on what the length of the password being used was.

Of course it is easy to make this more complicated, for example, possibly taking each letter in the password, giving it a numerical value multiplying it times 7, and dividing it by the length of the password, then rounding it up or down to get the corresponding key to be used.

If I can get the system this far, there are many things that can be added to further complicate the code. I think the first step would be to add nonsense letters into the code. If everything is one for one, at the very least, you can tell exactly how many characters are in the message. However, if 10 - 50% of the characters listed in the text are nonsense characters, then it is very hard to determine the true length of the message.

Of course, adding additional key pages would complicate the code exponentially.

Anyways, that's where I am at right now. Any help is appreciated.


Hello,

I have the following problem:

in column A I have series of numbers separated by commas (1,4,7, etc.). The numbers range from 1 to 27. I need to find in a separate column if, for instance, the cell A2 contains the number "1". I tried various formulas like find, search, etc. the problem is that the results is "true" even if, for instance the string in the cell is "2,7,14". I mean I need to find only occurrences of the exact number in the string. Hope it is clear.

Thank you.


I have an excel spreadsheet I want to make multiple columns of data change all from all caps to just the first letter of each word caps. I tried using the PROPER function as directed but it's not working.. I can only get it to do the function for one cell at a time. I put in to go from A1:A798. It only does A1. What am I doing wrong?


Does the PROPER function (or the VBA equivalent) work differently in different countries or does it always apply the same fixed logic of capitalising the first character of each word?

The reason I ask is that I'm based in the UK, but I work on data from UK, France and The Netherlands.

When processing data each month, one step in the process is to convert a number of columns to Proper Case. This is done either using the PROPER function (if there's not much data), or the following VBA:
Code:

For Each c In Selection
    c.Value = StrConv(c.Value, vbProperCase)
Next c


This works fine on my English data, but the French and Dutch language have different rules for what is and isn't capitalised (for example, in Dutch, letters "Ij" are considered a single character and if they're the first two characters in a word, both should be capitalised). Similarly, in French, certain words beginning l' need to have the l (L) in lower case.

Using wither of these methods of converting to proper case results in what are technically gramattical errors in these other languages, just as having text like "tHis tExt cApitalised" would be wrong in English (NB: My code doesn't actually do that to text! That's just an example of how incorrect capitalisation in English would be incorrect IF applied in that manner. Just as reading that text in English appears incorrect to an English speaker, the conversions that I do perform on the French and Dutch data will appear incorrect to French or Dutch speakers).

If PROPER or VBA Code does work differently in different countries, is there any way of forcing a UK English setup to apply the Dutch or French capitalisation rules?


In my spreadsheet, I'd like for columns L thru AX to be Proper Case. I'm having trouble with the proper code. Some code I've seen will work for a single column, but not multiple columns. Is there any way to do this?


Hi all,

I have a bizarre problem. I have a column where each row/cell contains a sentence (or two). These setnatces are currently in proper case (i.e. "They Look Like This"). Really I want the entire string to be lower case unless the character comes after a period/full stop.

Example:

"This Is How The Data Currently Looks. Which Is Wrong" would become "This is how the data currently looks. Which is wrong"

Any ideas chaps?

Ta
D


Hey all,

I have a cell range that is passed as a String to a function, and within that function I need to extract only the Column letter. If it was just 1 letter it would be simple, but it may be 2, so does anybody know of a way of testing to see if the second character is a letter or a number?

Thanks.


Hi All,
When the data capturers are entering data, they type with the caps on, afterward I use the Proper function to correct this, is there a way to have them typing it correctly from the start? I was thinking about conditional format, but I cant seem to get it right with the Proper function.
Thank you.


Two issues here I can't explain.

Issues 1
-----------
I have a class defined in say in module "myclass"
One of the methods is say method1, which has been declared as

Public sub method1(byref parm1 as long, byref parm2 as string)

Also in a test module I test the method1 as follows:-

Code:

dim tst as mylass
dim tstParm1 as long
dim tstParm2 as string

tstParm1 = "..some number..."
tstparm2 = "any string"

set tst = new myclass

tst.method1(tstParm1, tstParm2)


***********************************
I get a "compile error expected ="
Which seems to mean I have to assign a var to capture the return value from method1....but I haven't defined the sub to return a value (that's called a function)??

If I do assign a var to = the return value (i.e. var = method1...), the syntax error disappears but I get a runtime error - "Expecting a function or variable" as there is no return value from the method (i.e. not a function). Only way around this is to declare the method as a function and give it a return value.

Also, the EXACT SAME setup above but with just 1 parm (i.e. defined method1 to take JUST parm1 ) everything works as it should using a sub and not a function.

In summary, it would appear I can only declare a sub method if it takes only 1 parm, otherwise I have to declare a function - what am I missing or why is vba behavin g this way? Perhaps try it in case it's something to do with my setup only.



Issue 2
---------
Stumps me even more involving collections and number to string conversions
Here we go.....code is:-


Code:

dim data as variant
dim mycol as collection
dim nclass as myclass


set nclass = new myclass
set mycol = new collection

' ThisWorkBook.Sheets("sheet name").Usedrange contains various data.
' Column 1 contains 10 digit numbers from csv file. Format is "general"

data = ThisWorkBook.Sheets("some sheet name").Usedrange

nclass.number = data(1,1)   ' for e.g.
mycol.add item:=nclass key:=<string of nclass.number>  'note the index is the string of the number


later I try to access an nclass object from the collection via the key ...

mycol(data(1,1)) - gives me a subscription range error.

Ah ha this means data(1,1) must return the number stored as a number, not a string. So to get around this I use CStr(data(1,1))....although it doesn't seem to return a string either or at least I get another range error.

I've also tried Str which doesn't resolve issue either.

If I use mycol("<actual number>") this works.

So why isn't the CStr or Str doing its' conversion work or how else can I get around this?


Any help much appreciated.....

Thanks,




Without VB coding, I need to run a simple SUMIF where the condition is case dependent. Please see the attachment.

I've tried EXACT, but that's not for this purpose.

Thanks for the help.

Hi All

I am querying an Access database using ADO in Excel VBA.

Is it possible to use a user-defined function in my SQL command text? I know I could house the UDF in Access and then set-up the query there, and then use excel to run that existing query, but I wonder if it is possible somehow to actually use the user-defined function directly in my SQL string in my Excel VBA sub-routine.

My user-defined function is just basicaly a long Select Case statement, so I could use a really long IIf statement in my SQL string but the UDF is so much tidier.

Any ideas?

Thanks
Jon


Greetings!

I really appreciate your help in advance... I am working on a database with hundreds of entries on it, there is a column that has names on it. now i need a command button that when i click, the active cell will be changed in a Proper Case format, can you help me giving a "simple" code that will make it?


Hi, I was wondering if I could get help with a VBA project I'm working on to write a macro script that will check the string within a cell to see if conditions are met and then color the cell if any of them are met.

I have some programming experience, but I don't know the syntax of VBA very well. I'm having difficulty figuring out how to activate cells, shift down to test the next cell, etc.

There are 10 conditions, mainly of 3 types.
1) Is there a "Q" in the string
2) Are there multiple "C"s in the string
3) Are there more than 20 characters (ignore spaces) in the string

Essentially the macro will select cell "i2", test the cell text (containing a string of capital letters and spaces) for the 10 conditions, and color the cell background red if any conditions are met. Then it will check i3, i4, i5,...i500. Then it will move to k2, k3, k4....k500. M...O...(increment by 2 letters) until AC2...AC500.

My work so far:

I have a variable defined for each letter (ex: Ccount), a total length variable for checking total length, and an errorcount variable will increment from 0 if any conditions are met, and the cell will turn red if errorcount is >0 at the end of the cycle for each cell. Then it should set everything back to 0 and redo the process.


If someone could help with the outlining for the first 3 conditions, I can implement the others since they're very similar.


I already have the functions written out for each condition, but since the program will be checking hundreds of cells for all 10, I figure a loop in a macro in VBA would be more elegant. However, if there are suggestions for using my current functions (lots of len() ) those might be useful instead.


Ok I need something that basicly does the same thing as the EXACT worksheet function but on an unlimited number of arguments.

I have a row of approx 15 numeric values. I need a formulat that will look at those values and return FALSE if they are not all equal. True if they are all equal.

A1 A2 A3 A4.... Result
1 2 1 1 FALSE

A1 A2 A3 A4.... Result
1 1 1 1 True

I found the exact function but it only allows 2 arguments.
any thoughts?


I am having a very weird problem with the table in the attached file.

The table contains 5 columns titled INFO 1-5.

When I code a VLOOKUP function, some of the columns return the proper value. However, some of the columns return a 0 .

No matter what I type in the column, or even if I paste this table in a new document, I cannot get the proper value to show.

Does anyone know why? and how to solve this?!


I've got a string that looks like the following:

ABC DEF GHIJ 01234567 ABC DEF ABCD 1/02/2011

I am trying to extract the 1234567 from this string and I'm having some trouble with the mid formula. Many of the strings in the column are different lengths, so I need the number of characters to be higher, like 80, but in the case of this string, 80 characters finds a number, then a date, and returns #VALUE. I need a macro that will find and replace all dates with "", if possible. Or maybe it would be better to remove the second string of numbers in the string, if that's possible, since I believe some of the strings will have 01234567 and then further down the string might have 56789. Any ideas?

Here is the mid function I'm using:
{=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))}