I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.
Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.
I would like to change a number to a letter and then drop a digit from the end.
Say my data in A1 reads 81234568, and I would like it to display in cell A2 as h123456.
I have letter and number combination code in two collumns and they differ for 10.000 numbers:
BAM98314
BAM88314
BAM90000
BAM80000
As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.
How to translate a specific number into a specific letter related to the number itself.
I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.
So if the letter
B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course
B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course
P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course
P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course
and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.
The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.
and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course
and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.
I have a Col where the cells have been formatted to enter "PR" before the numbers input into any cell in that Col. I did this by using the custom selection in the format cell. However some times i need to input the number with the letter "A" at the end. For instance PR123456A.
Now in some cells it lets me do this but in others it does not. It would end up with 123456A, loosing the PR at the front. I have checked that all is well within the custom window. Can any body offer an explanation as to why it could be doing this.
Is there any way to name cells with same letter but different number?
e.g i need to name the first row A1 to A100.
I need to make a series of numbers and letter auto fill in a spread sheet
Example
2650A
2650B
2650C
And so on.
I have a table like this.........
Item Code
Ice A
Ice A
Ice B
water D
water A
water C
water A
milk B
milk D
milk C
milk C
milk C
milk B
And I would like to count the number of each letter to look like........
_______ A B C D
Ice ___ 2 1 0 0
water _ 2 0 1 1
milk ___0 2 3 1
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 have records combined with letter in column A: number is always on left and letter always on right, such as 456UI, 4689746COMPREH. How can split the combination into number in column B and letter in column C?
View 2 Replies View RelatedI made a calendar, and I need a letter next to the daynumber. How could I put a letter next to the number? The problem is that I have a formula for the daynumbers, how could I add the letter?
View 9 Replies View RelatedI'm trying to add plus 1 to a cell from another cell but it doesn't work because the number starts with a letter. I do want to keep the letter in there for a reason so removing the letter to make it work is not what I want.
Example:
Code:
ThisWorkbook.Sheets("Invoice").Range("H5").Value = ActiveCell.Value + 1
Is it that I need to declare and set the ActiveCell value as a string or something?
The following displays a name and number as the Chart title with VBA...how do I change the code so that is it name plus letter...instead of being Group 1 , Group 2 etc..etc.. it will be Group A , Group B etc..
Code:
.ChartTitle.Text = "Group " & lng + 1
Is there a way to get the column Letter, instead of Number?
like in A1 Column() would equal 1 in B1 Column() would equal 2
I would like
in A1 Column() to equal A and in B1 Column() to equal B
I have a list of names and I need to know how many names are greater than 6 characters in length. What is the formula I need to enter?
View 9 Replies View Relatedis there a way to make the following code return the letter of the column instead of the number? currently if the 'String' value that is in 'ColumnFind' is in column B this code returns a value of 2. i Need the 'B' for later code to work.
ColumnLetter = ColumnFind.Column
I need to figure out the number of occurrance of a letter in a word written in a cell. For Example i am writing "pattern" in a Excel cell. I want to know the marco/vba code that will give me the number of occurrance of each letter. The output should be:
p=1
a=1
t=2
e=1
r=1
n=1
I'm trying to set a data validation in B1 from values in A1:A5.
A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes
What I want is show in dropdown list the words in singular (without the "s" at the end).
The formula below works if I introduce it in a normal cell,
[Code]....
or as array formula with CRTL+SHIFT+ENTER in this way
[Code] .....
But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.
I am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)
In the A column, it looks like this:
CV42 6AQ
FC45 D4D
West yorkshire
PR42 6RD
Etc.
i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.
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"?
I have a spreadsheet with information in columns a-x. In column A there are part numbers like: RH630-34, PH630-343, 6-255, 16-01, 72500, There are may combinations of just numbers, and numbers first letters second, and letter first number second.
All usually seperated by a hyphen. The entire spreadsheet will be sorted by Column A first.
I need to sort them so the order would be numbers first and combo with number letters next. finish product: 6-255, 16-01, 72500, PH630-343, RH630-34. Is this possible? I have seen other posts and suggesting putting spaces before the numbers. That seems to work but in the case of 6-138 and 6-1038 the 6-1038 is first
I have a sheet which calculates payment amounts.
Column titles:
Hours | Rate of Pay | Total
In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.
What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.
H=2 x rate of pay = total
I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.
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?
View 5 Replies View RelatedI WANT TO INSERT A LETTER IN FRONT OF A NUMBERS THAT ALREADY EXIST IN CELLS
IN A COLUMN. SORT OF LIKE USING "FIND & REPLACE" EXCEPT THAT I DON'T HAVE
ANYTHING TO REPLACE; I JUST WANT TO INSERT A LETTER PREFIX IN FRONT OF
NUMBERS.
Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?
=INDIRECT(row() & column())?
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?
Without using VBA code, is there a way to display or find the worksheet number of the active worksheet you are viewing? All my sheets have names, and I have a lot of them.
When I want to loop through a set of them with code, I want to know what numbers they are beforehand.
When using the formula '=COLUMN()' in cell A1, it returns the number of the column - in this case, '1' (for column A). Is it possible to affect this formula so that it returns the column letter (in this case, 'A')?
ce.Offset(, 4) = "=" & ce.Offset(, -1).Address & "*" & ce.Offset(, 4).Column & "25"
If X29 were equal to ce.Offset(,4) then the value in that cell should be set to
"=S29*X25"
Currenctly it is returning "=S29*2425"
(FYI - ce is just a variable to capture a particular range/cell that is dynamic and used within a For Next Loop)
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?