I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?
How to calculate a check digit in excel. The details to calculate this are as follows:
All variables in the calculation are positive integers.
We take each integer of the pro number and multiply it by a value and sum them to get a total.
An Example: 8 Digit Pro Number: 66988757 Pro Number: 6 6 9 8 8 7 5 7 Position in the Pro Number: 1 2 3 4 5 6 7 8 Multiply each digit in the pro number by (10-position) in the number, to achieve a sum.
Using our example pro again: 6*(10-1) + 6*(10-2) + 9*(10-3) + 8*(10-4) + 8*(10-5) + 7*(10-6) + 5*(10-7) + 7*(10-8) 6*9 + 6*8 + 9*7 + 8*6 + 8*5 + 7*4 + 5*3 + 7*2 54+ 48+ 63+48+40+28+15+14 = 310 Take the Sum of the previous calculation and divide it by 11 310/11 = 28
(Actually, it's 28.181818, but since we're working with integers, we truncate everything behind the decimal).
Figure a remainder by multiplying the quotient by 11 and subtracting from the sum. Remainder = 310 - (28*11) ---> Remainder = 310 - 308 ----> Remainder = 2 Check Digit = 11- Remainder Check Digit = 11 - 2 ---> Check Digit = 9 Note: if the check digit is 10 or 11, need to subtract 10 from the Check Digit.
I have a column of 6 digit numbers in excel, and I need to remove the last digit from each number, turning it into a 5 digit number. No rounding, just simply remove the last digit. Each number is different. Does anyone know how to quickly and efficiently remove the last digit from each number? I can convert to alphanumeric string if need be...
I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").
I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).
The statement I had was: =if(A1>=50,1900+A1,2000+A1)
I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.
It seems like it should be pretty simple, but I don't know how to word it in a macro.
I have an address (city, state, zip format) in a cell. Some of the zip codes only have a 4-digit zip code which is the way the file was received. I know these zip codes have leading zero. How can I convert these to a five-digit code. Examples are as follows:
Waltham MA 2453 convert to 'Waltham MA 02453'
Boston MA 2210 convert to 'Boston MA 02210'
CEDAR GROVE, NJ 7009-1174 convert to 'CEDAR GROVE, NJ 07009-1174'
TEANECK CITY, NJ 7666 convert to 'TEANECK CITY, NJ 07666'
I have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it.
Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with).
I have in column " A" 500 rows with numbers in random sequences each sequence has random number of rows , each sequence is seperated with a blank cell (the result of a formula) some sequences have zerrows included. I need to find the lowest number in each sequence and put that number in column
" B" I also need to find the highest number and put that number in column " C"
I would like to create a column with letters from alphabet in a sequence. If I write A and in cell below I put B then highlight the two cells and drag down I get a repetition of A and B. How do I get the following alphabet letters ie. C,D, E etc.?
I have Excel 2003. I am trying to find the next number in a sequence of numbers. The number range is 1-59, and the sequence is 89 numbers that go like:
I have a dynamic worksheet containing a variable number of rows that I would like to have automatically numbered when I run my VBA script. I used Dave Hawley's code as follows, with a modification to begin at cell A2, but it doesn't seem to work, as it only numbers cell A2 and not the rest.
VB: With Range("A2") .Value = 1 .AutoFill .Range("A2:A" & RowTotal), xlLinearTrend .Range("A" & RowTotal + 1).Clear End With
RowTotal is just a variable that holds the results of a simple last row finder. How to make this work?
I was gonna explain it but I couldn't make myself clear so I took a screenshot:
sequence.PNG
folder_type is what the user types, (for example, the letter "A"), and folder_code is a sequence that should auto-generate according to what was manually inserted in the folder_type field.
I want to create a 4 character alphanumeric sequence. The sequence is to be like this: a1a1-z10z10. If it's a number, increment 1 until you reach 10, then move to the next column, if it's a letter, increment a until z, then move to the next column. Excel's autofill feature doesn't seem to work well for me due to the letters involved, so if someone has a macro handy that would be great.
formula that will produce the number sequence as shown in the third column of the table. The sequence starts at 1 and if the colour changes then the number is 2 and if the colour changes again then the number is 3 and so on. The part I'm having difficulty with is that if the name in the first column changes then the sequence must reset to the number 1 and become 2,3 etc. as above when the colour changes.
I have a column of values, and I want a column next to it that shows the value in every second cell in the first column, but right underneath. See below for an example of what I am trying to achieve. The actual column is 17000 cells so I cant do it manually.
I want to change the sequence of find for the below code. I want to find the last entered row, instead of first entered. Now this code find first entered row.
I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.
E.g. if a person is absent for 1 day the formula assigns a value of 1 if a person is off for 3 days in succession the formula assigns a value of 1
code number (as text) desired result date condition cost ($)
'1003
1 31/12/2000 1 5
[Code] ..........
if "code number", "date","condition" and "cost" the same line of rows so "desired result" = 1 like code "1003" or so code "1005" = 5, otherwise if there is a no match/same condition "desired result" start from = 2,3,4,etc.......
I want the numbers to increase based on the labels from a list, such as the first john chosen will get a 1 on the first cell on sheet 2, if john is chosen again the 2nd john will get a 2 on the next cell on sheet 2 and so on for the other names.
I tried to use IF(COUNTIF(D:D,D2)=1,LARGE(IF(D:D=D2,E:E)+1),1) but it didn't work out.
I need a macro, preferably a "sub" that will search a long text input and when it finds the first instance of the letters "GSH" in that order removing all previous text before it. The amount of text characters, letters, and order before the gsh will vary.
