Cell Format: Input The Number With The Letter "A" At The End
Oct 11, 2008
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.
View 2 Replies
ADVERTISEMENT
Jul 11, 2009
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.
View 14 Replies
View Related
Jul 22, 2014
I am a teacher and I want a column to turn a particular colour if a pupil has met their target grade and another if they have not. Our grades work as letters A* being the best then descending as follows A, B, C, D, E, F, G, U.
I have a column with the pupil's target grade (one of the above letters) and another column with their current grade. How do I get excel to understand the ranking of the letters and format the current grade column according to whether they have met their target or not?
View 8 Replies
View Related
Oct 15, 2009
Well following on from my question earlier, I am still playing with my timesheet/rota I'm developing.
I have a start time and an end time, which is formatted as hh:mm then an hours cell which adds them up for you.
BUT if someone is on a rest day or a holiday day or a sick day etc, I would like them to be able to type that in without the cell that adds the hours up going doo laly!!!!! (######)
So, is there anyway they can type say 'R' and then getting the hours total cell to ignore it because it isn't a time?
View 4 Replies
View Related
Oct 14, 2008
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 Related
Nov 5, 2008
I am trying to restrict a cell to only be able to input the letter "i" multiple times, almost like a tally sheet, in other words I want the cell to be restriced to one letter, but allow that letter to be entered multiple times.
View 4 Replies
View Related
Oct 4, 2012
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.
View 3 Replies
View Related
Jan 27, 2006
I 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.
View 9 Replies
View Related
Dec 3, 2009
I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10
[A4]=B(row())*K(B1)
View 4 Replies
View Related
May 31, 2014
i have this in my 1 cell: ttgtcctacttacaacactgtgcttagtaatggttattgcgactttatccttgttctgaa i want to count how many "a" in this cell . which formula i can use to solve this problem ?
View 8 Replies
View Related
Oct 3, 2008
Need a function that would insert a letter or a number in front of numbers in a cell for example
column A
3245
I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with
Column A
S3245
View 3 Replies
View Related
Dec 30, 2008
i have a cell that i have to put a lead time into ( weeks ) ie 7-9 if i type this into the cell it throws up all sorts of things but i want it to display 7-9 trouble is i know i could do ="7-10" but other users use it and wont now that how can i format the cell so it just displays what is input?
View 3 Replies
View Related
Dec 30, 2009
i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.
View 9 Replies
View Related
Mar 24, 2007
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View 2 Replies
View Related
Apr 3, 2014
I'm trying to write an IF formula that will return a number if the word in the adjacent cell begins with a specific letter. Here's what I want to show:
City
01
Express
02
Overnight
03
So "C" would return 01, "E" would return 02 and "O" would return 03.
View 3 Replies
View Related
Aug 23, 2013
Sometimes data comes in with various columns that need Concatenate.
I need VBA to prompt user to input which columns to use inside the formula:
Code:
'Place formula into A2
Range("A2").Select
ActiveCell.FormulaR1C1 = "=COCATENATE(D2&"_"&E2)"
'VBA to copy down formula until last row In my macro, D & E will vary, is there any way to make excel ask which two??
View 3 Replies
View Related
Jan 24, 2014
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
View 3 Replies
View Related
Jan 20, 2014
The cell content is to be 0-7 characters in length.
Alpha characters only, i.e., no numeric.
First character must be uppercase.
Remaining characters may be lowercase or blank.
Is that possible to do in MS Excel 2007?
View 10 Replies
View Related
Feb 25, 2010
Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.
When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.
Example:
B20= 1:15 (1h15m) NOT 1:15:00 AM
B24= :30 (30min)
B28= :45 (45min)
B32= 1:45 (1h45m) NOT 1:45:00 AM
B38= 4:15 (4h15min)
I have attached a copy of the form for better clarification.
It doesnt seem to be complicated, but it's certainly a lot more than I thought.
View 4 Replies
View Related
Dec 31, 2009
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
View 9 Replies
View Related
Jul 30, 2009
Is there a way to have a drop down or some sort of input cell that would change the look and format of the entire worksheet? Not sure how to pose the question?? For example.
What I'm working on is a departmental forecast sheet.
I want my department heads to be able to all look at the same excel file, then be able to input or select their department from a drop down. Once the department is selected that would call for excel to bring up the forecast for their specific department (**Each departments sheet is a little different).
View 9 Replies
View Related
Oct 20, 2013
I have data in cell A1 that often changes its number format(from $ to %), can i have the number format change in cell B1 whenever the number format in cell A1 changes?
View 3 Replies
View Related
Nov 5, 2013
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.
View 3 Replies
View Related
Jul 30, 2009
I am working on a spreadsheet and would like to create a cell that allows the user to input a number (1 to 5) which will then equate to a percentage(%). I would then like this to deduct that percentage from a total number...
To try and explain this.. Should the user input 1 into the cell, this would then equate to 3% - this would then deduct from a total that percentage. Input 2 = 5% etc..
View 12 Replies
View Related
Dec 21, 2012
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.
View 2 Replies
View Related
Feb 3, 2014
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.
View 9 Replies
View Related
Jan 20, 2009
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.
View 2 Replies
View Related
Apr 7, 2014
In 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.
View 4 Replies
View Related
Dec 15, 2008
I have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?
View 3 Replies
View Related
Jun 21, 2014
I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.
View 1 Replies
View Related
