Extracting The Last 2 Digit

Jun 2, 2009

How do you remove the last 2 digit

A B
12345688 88
12548963 63
14552365 65

So in column B is would show

88
63
65

View 3 Replies


ADVERTISEMENT

Seperate 2 Digit Number Into 2 Single Digit Numbers

Apr 3, 2008

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?

View 4 Replies View Related

Check Digit For 8 Digit Number

Jun 13, 2014

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.

View 3 Replies View Related

Remove The Last Digit From Each Number, Turning It Into A 5 Digit Number

Mar 2, 2009

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...

View 4 Replies View Related

If / Then In A Macro - Changing 2 Digit Year Into 4 Digit Year

Jul 3, 2014

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.

Starting Column Example:
12

13

14

99

11

[Code] ......

For some reason I can't get rid of the borders...

View 6 Replies View Related

Extracting Data From File In Directory And Extracting Filename

Mar 20, 2014

I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.

In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.

View 4 Replies View Related

Convert 4 Digit Zip Code To 5 Digit Zip Code?

Apr 25, 2014

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'

View 3 Replies View Related

Converting 2 Digit Year Into 4 Digit Year

Apr 15, 2008

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).

Example:

98 --> 1998
99 --> 1999
00 --> 2000
01 --> 2001

View 9 Replies View Related

Select ONLY 2nd Last Digit

Aug 28, 2012

Say I have a sets of numbers in column A as below:
5496523
65325555789

I know the formula to select the last number:
=RIGHT(A1,1) gives the answer 3
=RIGHT(A2,1) gives the answer 9

To select the last 2 numbers is:
=RIGHT(A1,2) gives the answer 23
=RIGHT(A2,2) gives the answer 89

My query is to select ONLY the 2nd last digit (being 2 in A1 or 8 in A2).

View 8 Replies View Related

12 Digit Calculator

Feb 20, 2007

Is there a way to build a 12 digit calculator in excel?

View 9 Replies View Related

15 Digit Sequence

Mar 31, 2009

My question is, a formula was provided to me that add's a comma and a space after a 15 digit sequence of numbers, that formula is:

=IF(MID(C378,LEN(C378)-1,1)="W",C378&" ,",C378)

And what that does is it would change data in a cell that looks like this
01-02-034-05-W2 TO 02-03-045-06-W2

To this:

01-02-034-05-W2 TO 02-03-045-06-W2 ,

The formula works great, thanks to the help of the posters!

However I just ran into an issue, what if I have a set of data in a cell that looks like this:

01-02-034-05-W2 TO 02-03-045-07-W2
02-02-034-05-W2 TO 02-03-045-07-W2
03-02-034-05-W2 TO 02-03-045-07-W2

And I need a space and a comma at the end of each sequence, so the above would change to this after the formula is applied:

01-02-034-05-W2 TO 02-03-045-07-W2 ,
02-02-034-05-W2 TO 02-03-045-07-W2 ,
03-02-034-05-W2 TO 02-03-045-07-W2 ,

Now the data in the cell is wrapped so it lists one set of numbers, then the next set, then the next set as seen above.

View 9 Replies View Related

Remove A Digit

Oct 9, 2009

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 .

View 9 Replies View Related

Is A Number Or A Digit

Jun 22, 2006

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")

View 3 Replies View Related

Sorting By First Digit Only

Sep 21, 2006

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.

View 6 Replies View Related

Add Specific Number In A Digit?

Jun 9, 2014

Well I want to add few numbers in a few number for example I have ten columns containing few cell numbers and i want to add 92 my country code with it. I have tried this by using replace formula but in result it comes in text format from which i cannot use another formula unless text format is removed and for that i have to do it one by on in every single cell. I have attached a sample sheet.

View 4 Replies View Related

Return First Digit In Cell Value

Aug 23, 2014

Imagine a cell has a value of "008281" at A1

I want to return the first digit of the 6 digits in A1 which will be "0" in A2.

I tried using the formulae in A2 with "=left(A1,1), it returns "8" instead.

View 2 Replies View Related

Add The First Digit In Range Of Cells?

Dec 10, 2013

I have data in cells A1:Z1. The data is all 1 digit numbers, but sometimes they have asterisks behind the number. For instance in cell A1, I have 4, but in B1 I have 3**. I want to take the first digit of each cell and add it together and the result to be in AA1. The only thing I can think of doing is =SUM(LEFT(A1), LEFT(B1)...) but that's going to take too long (especially as I add columns).

View 3 Replies View Related

Counting Occurrences Of Particular Digit

Dec 12, 2008

I have a slight problem...I have a range of values..

0113
1240
8430
0903

I need to count the occurance of a a particular no. from 0-9.. So i want to know how many times say 0 appears in that range of values etc so on till 9. I tried using CountIF but the problem i face is lets say in my cell 0903 there are 2 0s inside, it doesnt count this 2 zero..

View 14 Replies View Related

Get One Cell To Increase By Digit One

Jan 10, 2009

How can I get one cell to increase by 1 after I change the date in another cell. The spreadsheet is used to keep track of Oxygen Bottles at the local Fire Dept. and I want the " Times Filled" cell to increase by 1 after the " Date Filled" cell is changed. I'm not sure if they key the date in or if they use a calender add-in pop up.

View 5 Replies View Related

6 Digit Code To Date.

Dec 8, 2009

I have many dates in a column in a 6 digit format such as:

950223
950224
950225

These dates are Feb 23, 1995; Feb 24, 1995 etc.

How can I convert these dates in the column to 2/23/1995, 2/24/1995 etc?

View 3 Replies View Related

5 Or 6 Digit Number To Date?

Dec 13, 2013

I have the following dates in a column and need to convert them to the short date format:

91113 (Sept 11, 2013)
100713 (Oct 07, 2013)
122612 (Dec 26, 2012)
40413 (April 04, 2013)

Those are just some examples. When I convert these as is I get dates that are really far in the future. Some dates are 5 digit and some are 6.

View 7 Replies View Related

Delete 1st Digit In Column

Jan 17, 2014

I have column b filled with 17000 plus numbers formatted as text. I need an easy way to delete the first character on the left in each cell provided it is a zero.

View 2 Replies View Related

Keeping Two Digit Number When It Is 1-9

Aug 19, 2014

I have the following

[Code] .....

AK5 value will be 2 - 21.

I need it to display

A1D05

or

A1D21

or whatever the number is after A1D, but when AK8 = a value of 1 or 2 or 3...... It comes back as A1D1 or A1D2... How ca I keep the two digits in AK8 to stay and put the value the way I want in AE5 ...

View 5 Replies View Related

Converting Digit To Hour

Nov 3, 2006

I have simple (or not) question:
how can I convert a digit that I input into a cell to an hour format ?

I want to achieve something like this:
- when I input a digit into a cell , for example: 9 a want to convert it to 9:00 (9 hours, 0 minutes). How can I do it ?

View 11 Replies View Related

Split Out 5 Digit Numerical Only?

Apr 2, 2013

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  

View 6 Replies View Related

5 Digit Date Conversion

Nov 14, 2006

IS THERE A FORMULA TO CONVERT THE 5 DIGIT # INTO ITS CORRESPONDING DATE?

View 9 Replies View Related

2 Digit Number Combination

Jan 26, 2009

I'm trying to figure out how to setup a worksheet to find the most common 2 digit numbers going vertically from the bottom(cold) to the top(hot) it would consist of 90 digits 0 thru 9

it would look like this

4 0 3 9 0 4 3 3 2
9 2 5 6 5 6 9 6 6
8 9 9 3 1 0 2 9 8
1 6 7 5 9 9 8 2 5
2 7 2 2 2 8 5 1 3
0 1 4 7 4 7 6 0 9
3 5 6 4 3 3 0 4 4
7 8 8 1 8 5 7 8 7
5 4 1 8 6 1 1 5 1
6 3 0 0 7 2 4 7 0

each vertical line would be considered weeks 9 thru 1. week 9 would be the first vertical line of digits on the left. it could also contain the most common 2 digits horizontally. Both 2 digit values would be color coded ex. blue equals most common 2 digit horizontally and green equals vertically. I would also like to color code the most common 2 digit value diagonally as long as it is the most common of either the vertical or horizontal 2 digit. Each number is seperate on the worksheet they would not be pairs. im using excell 2003.

View 9 Replies View Related

Change Last Digit Of A Number To 5 Or 9

Jun 26, 2009

The last digit of a number (can be any demonination but always an integer). I know that you can check the last digit of a number with =RIGHT(A1,1) but I would like to check whether the last digit in number is 1,2,3,4,5 and if so change the whole number to a number ending in 5. If the original number ends in 0,9,8,7,6 then I want it to be changed to end in 9. Is this possible and if so is there an excel function that I could use??
e.g. 143 -> 145
e.g. 1037 -> 1039

View 9 Replies View Related

Calculate The Check Digit

Nov 1, 2009

who can provide me a full formula enabling me to calculate the check digit(MOD 10) of the a given number?

View 9 Replies View Related

How To Calculate Check Digit (MOD 10)

Aug 19, 2003

How do I create a formula that will calculate a check digit for a 9-digit number using MOD 10? The weighting is 212121212. The problem I am having is that if the sum of the product is in double digits, they must be treated as individual digits.

Example:

Number To Be Calculated: 0 0 4 4 5 5 5 2 5

Weighting: 2 1 2 1 2 1 2 1 2

Sum: 0+0+8+4+1+0+5+1+0+2+1+0

22/10 = 2 r2
10-2 = 8 (Check Digit)

Is it possible to create such a formula? I need to calculate several hundred and do not want to do this manually!

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved