I am trying to find a way to turn the raw data below, into the results on the right. Specifically I would like to extract any 5 digit (denoted by "." as a break) numrical value from the cells in column A (there could be as many as 20, 5 digit numurical values, id like each one listed in b2, c2, c3, and so on)
Â
ABCD
1Raw Results  Â
2abc.k(p).nov20.99375.65487.12354.jan13.p 99375 65487 12354Â
3edf.jun20.99815.p 99815  Â
4abc.s.jan40.65841Â 65841Â Â Â
5tge.s.96514.jan15.96548Â 96514Â 96548Â Â
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'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1: ######################################### Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe". #########################################
Part 2: #################################################### Column C contains addresses in the format: "#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210". ####################################################
Part I... I got the answer 1987, and now I want Excel to take the numbers out and display... 1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.
How do I split 1987 and put the numbers in different boxes?
Part II... I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.
So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)
(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)
So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?
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).
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g: A2: aa,ab,ac
String Value aa 1 ab 1 ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
I have been searching to see if it's possible to create a histogram with non-numerical data and it doesn't seem like it's possible. Is there something similar where I can. Count number of occurrences of a string and organize them from most frequent to less frequent? There are about 60 possible strings.
e.g. ABCDEFGHIJ 1ddhykygghd 2jlqqwertya 3ukhghjklzx 4tukyuuiioa 5xypyjkkadh
Then output the counts of each of the strings (letters in this case) present. I was thinking of just putting them in the same row or column and manually counting, but it doesn't seem like it would be that difficult have it automated.
Is there an equivalent of =rank for non-numerical data? If not, is it possible to sort data (into another column if necessary) alphabetically without resorting to a macro?
I have column a with 1000 ten digit numbers. I need to remove the digit in the fourth place from the left side in the entire column. I don't know how .
how you can determine if a cel value is a number or a digit
It is actually for a column to determine if it has a heading or not and acordingly y have to count the rows off that "current region" and put some values with a formula next to this column.
Y was testing an expression with this
msg = IIf(target.End(xlUp).Value = "X", "is this a LETTER", "is this a DIGIT")
I am trying to sort a spreadsheet in descending order of a certain column. I have been able to do so quite easily in the past. However, for some reason, when I am trying to sort it now, it is sorting based only on the first digit of the number in the cell. For example, the order after sorting may end up as 90, 9, 9, 82, 8, 8, 8, 74, etc. I don't know what happened to cause the change, or how to fix it.
produce graphs/charts based on inequalities monitoring information - gender, nationality, age etc. I'm capturing info from several events, one sheet per event. Had a look at COUNTIF but not sure it's what I want and looks like I'd need sub-sheet for each? Capture.PNG
I'm working on a spreadsheet and I want the letter "E" to have a numerical value of zero, and not necessarily in just place as my sheet will be regularly updated. If you're a fan of golf, you will understand what I am doing. I'm basically making a custom leader board for a fantasy golf game that I'm doing, and I'd like for the letter E to represent the value of zero when I am adding up the scores.
For example A1: -1 A2: -3 A3: 4 A4: E A5: -2 A6: 3 A7: 3 A8: -5 Total: -11 (have a formula set up to only count the best 4 scores).
I want to be able to add up those columns, and since only the lowest 4 scores count, I need E to equal zero. I get that I could just put 0 there, so I'm being a little picky (in golf E represents Even par, essentially a value of zero for the purposes of this game). There are 20+ teams in the game so I need E to be recognized as zero in each of those fields.
I have a column of dates (2007, 2006, 2005 etc...) and a column of data ranging from "unsatisfied" to "very satisfied". I want a formula to provide, for example, the total number of occurences of "very satisfied" in "2006".
This should be easy but after searching "consolidate" all that I could find was multiple sheets and complex questions.
I have 5 data columns that I need consolidated into 1 single column. I've tried the consolidate funtion but it appears to only work with formulas and numerical values (unless I cant find the proper command.) I've also tried paste special but it populates the cells following.
I need 5 cells word values pasted into a single cell
I have Cell B1 = 6 on a worksheet named DOC. On Cell B3 of worksheet Week 1 it has "Celina". Is there a way that if Cell B1 in DOC has number 6, Cell B3 on Week 1 will automatically become Celina. All worksheets are in the same workbook.Likewise if Cell B1 ever has any of the following numbers, cell B3 in Week 1 will display the corresponding alphanumeric.
I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!
Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?
