Convert the first letter of a string to a capital without altering the rest of the strings format.
example: the activecell contains the string a1st (lowercase a, superscript 1st)
how can i achieve this in VBA?
Additionally, is it also possible to convert all of a string to capitals except the superscript characters
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
View 9 Replies View RelatedI require a formula which finds the he first capital letter within a cell and returns the text, but I need it to ignore blank cells and the symbol *.
View 6 Replies View RelatedI was wondering whether it is possible to find a formula in Excel that would extract (using RIGHT formula) surname from Name Surname cell despite surname lenght i.e.:
'John Smith' in one cell would give 'Smith' in the other
'Tom Let' in one cell would give 'Let' in the other
I have tried to combine RIGHT, FIND and PROPER (for upper case) in one formula but it does not work.
I have many lines of text and I wondered if there is a formula so I can insert a comma before the first capital letter of each line? A small amount of text is below
leave on left Salter Road
right Brunel Road
What i would like is there to be a comma before the first capital letter so it reads
leave on left, Salter Road
right, Brunel Road
Is this even possible?
I have code that I just noticed will not work if user inputs a capital S. I have tried a few things but I can not seem to get it to work. Here was the original code:
If Range("H13") "s" Then
I tired this:
If Range("H13") "s" or "S" Then
I am trying to set up a worksheet that shows shifts using a Capital "D" and a lower case "d" (one is for a 12 hour shift, the other for only 6 hours). Is there any way to set up a Countif formula that makes the distinction?
View 12 Replies View RelatedIs there any way of writing a macro so that specific cells are formatted to start with a capital letter and end with a full stop. Can you also write a macro to automatically spell check specific cells?
View 11 Replies View RelatedI would like to sort words or data which has the First capital letter, words which has spaces and words which contains number... i have attached a sample file.
View 9 Replies View RelatedI just want to conditionally format some columns with different colours. The problem is that I have some cells that have the value 'a' and others that have 'A'. These should be different colours, but I can't seem to get Excel to detect between the two forms.
View 9 Replies View RelatedIn sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
Is there simple function that anyone knows of (or has written) that will convert a letter to its alpha-numeric equivalent?
For instance, A = 1, B = 2, AA = 27, etc (a = 1, b = 2, aa = 27)
I need to convert a column letter to a column number for the following line of code in my VBA application:
VB:
strAverageBaseFormula = "=AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":K" & Trim(Str(lngFirstRowOfIncrement + 1)) & ")"
I am inserting a formula into a cell using VBA, but the problem is that I don't know the letter value of column "K" (used in the middle of the line); instead, I know it as column number 11. Is there a simple way to convert the column number 11 to the column letter "K"?
How Do I Convert Date:
10/3/2008 into October
6/1/2008 into June
7/3/2008 into July
I have date 10/3/2008 in cell and if I convert format to M into Just gives me first letter of Month, so O, I want it to read October, etc.
Some bits of code I have learned use column numbers and some bits use column letters.
Can someone share a line or two that I could add to my macro that will convert the F representing column F into a 6, and vice versa, so that I can continue using my pre-existing bits?
I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number to a letter?
eg. somefunction(1) gives me column(A) as an answer?
a formula that would take letter grades (A, B, C, D, and E) and produce an average letter grade?
For example, a student gets a D on one assignment and a B on another. I want a formula that will produce "C".
Can I use a string in VBA just like C. I'm trying to get character one by one for a string. Can I do somehting like that here?
In C I use:
char string[20];
For ( int i = 0; string[i] != '"; i++)
cout << string[i];
I wanted to know if there was a way to either make a variable hold a column letter or else automatically convert a column number to a letter. This would be for use in the Range property.
All I could find is some complicated technique requiring division by 26 that I will never remember let alone code quickly.
Or if you have a variable holding a column number, is the only way to use it with Cells?
I have a drop down menu using data validation and the values are B,C,D
I want to keep the letter in a cell, but make each one become number, so B=5, C=10, D=20
I have another dropo down menu that contains 6,10,16
What I require is that once the 2 drop down menus have 'values selected' I need to multiply the 2 cells together
so for example
If B and 6 was selected, I require the 3rd cell to be 5*6 so what is the formula?
how I check the last 4 letters of a String to make sure they say '.xls', '.csv', etc, etc, for varification purposes.
View 4 Replies View RelatedIf I have the following in different cells of a worksheet:
ANFKLD
or
AAB
and so on
How do retrieve the second to last letter of any of these strings?
Is there an easy way (without writing my own conversion function) to get the column letter from a range object, as opposed to the column number?
View 5 Replies View RelatedFor icount = 1 To LenComputername
valComputername = Asc(Mid(UserComputername, icount, 1))
Next icount
If the computer name is NAMTOK-PC Then the LenComputername is 9. Does that mean then that the valComputername is equal to 78?
I use currency data pasted from legacy program that pastes dollar values followed by a C or D to represent debit and credits, ie -- 1,000,000.00C or 1,000,000.00D.
Is there some way to remove the letter and correctly format the value to a negative or positive number on a whole worksheet or column?
Currently I am doing this manually one cell at a time.
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.
Having trouble with this one. Searches seem to bring up every other variation of extracting info from strings except this.
I have cells which contain alphanumeric strings as below and, using a formula, I want to extract everything from and including the last letter to give A 2-3, B 3 and C 3-4 in the examples below.
Sub-base A 2-3
Sub-base B 3
Paving C 3-4
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).
View 4 Replies View RelatedLooking for VBA that can change the font size of "•" char(0149) within any string in selected cells.
Also looking to delete the last "•" char(0149) within any string of selected cells.
What would the syntax for these two functions be?
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies View Related