Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Calculate The Check Digit

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

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
How To Calculate Check Digit (MOD 10)
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.


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 Replies!   View Related
Macro To Force Format And Check Barcode Check Digit
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts

8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"

these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is

View Replies!   View Related
Sequential Check Digit Validation
I have a spreadsheet that pulls in a log file after running a macro with 100000eight digit numbers which may or may not have a record in the system. These numbers are produced by using any seven digits and adding a check digit at the end for an eight digit number using the mod function specifically Mod7...=MOD(A1,7) , for example, if cell A1 has 1234567 the mod7 function will produce the check digit 5 then concatenating for the eight digit number 12345675. the next number will be produced by adding 1 to the end of the 7 digit number 1234567+1=1234568 then concatenating again the number with the check digit 6, 12345686. this would be sequential order.

my spreadsheet gets all the eight digit numbers with no record and sorts them in MOD7 sequential order in one column. since the spreadsheet ignores the eight digit numbers with records. my compiled list of numbers have non sequential numbers in the list.

i need a way in VB to loop through 100000 lines (column A) and find the first and last number of a MOD7 sequential range and place the first number in (column B) and the last in (column C).

I have figured a way to do this in a spreadsheet but it takes too many functions and calculations in 100000 lines so the spreadsheet takes too long to open and sometimes crashes!

View Replies!   View Related
Check Digit For Container Numbers
I wanna create a buildin check digit for container numbers

so it will exists out of 11 cells. ( GATU 021097-9 )

is it possible when u type any letter/number in the first cell on a row it automaticly jumps to the next cell, repeating this for the full container number ?

View Replies!   View Related
Check 4 Digit Fiscal Year Is Entered In A Cell
This is my code. It is for checking that only a 4 digit fiscal year is entered in a cell. The code works fine except it only checks a max of three time. So if the user enters xxx it will prompt for a correct response three times, if xxx is entere a 4th time then that value appears in the cell. I was wondering why this is taking place.

Here is my

View Replies!   View Related
Combining Two Equations With MOD Function For UPC Check Digit Calculation.
Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,

View Replies!   View Related
Seperate 2 Digit Number Into 2 Single Digit Numbers
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 Replies!   View Related
Remove The Last Digit From Each Number, Turning It Into A 5 Digit Number
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 Replies!   View Related
Converting 2 Digit Year Into 4 Digit Year
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).


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

View Replies!   View Related
Remove A Digit
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 Replies!   View Related
12 Digit Calculator
Is there a way to build a 12 digit calculator in excel?

View Replies!   View Related
15 Digit Sequence
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 Replies!   View Related
Extracting The Last 2 Digit
How do you remove the last 2 digit

12345688 88
12548963 63
14552365 65

So in column B is would show


View Replies!   View Related
Is A Number Or A Digit
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 Replies!   View Related
Sorting By First Digit Only
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 Replies!   View Related
Counting Occurrences Of Particular Digit
I have a slight problem...I have a range of values..


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 Replies!   View Related
Change Last Digit Of A Number To 5 Or 9
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 Replies!   View Related
2 Digit Number Combination
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 Replies!   View Related
Get One Cell To Increase By Digit One
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 Replies!   View Related
6 Digit Code To Date.
I have many dates in a column in a 6 digit format such as:


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 Replies!   View Related
5 Digit Date Conversion

View Replies!   View Related
Converting Digit To Hour
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 Replies!   View Related
Add/Sum Each Digit In Long Number
I have a cell with a number which is a result of several previous sums. It's a number with 8 to 11 digits. For example: 878567663.

I need to add each digit of the number, and then add again the result until I end up with a single digit. In the above example it'd be (8+7+8+5+6+7+6+6+3=56), then (5+6=11), then (1+1=2) and put the end result (2) in a cell

My main doubt is that I don't know how to use a simple "do while" type structure in Excel to achieve the above.


1.) How do I create a formula to do that?. I have seen solutions in this board that let me add each digit but that's it... I need to "reduce" it until only one digit is left...

2.) What about if the lenght of the number is not always the same (fe. in one cell is 8, in another is 11, the next one is again 8...)?

View Replies!   View Related
Two Digit Year Changes On Import
I am importing data from a .csv file. The file contains dates like 02/15/40. The year is 2040, not 1940. Excel doesn't seem to use the Control Panel -> Regional Settings change when I open this file. I have my Regional Settings set to 2000 => 2099 to change to a two digit year. If I open a blank worksheet and type in 2/15/40 in a cell...Excel does the correct conversion to 2040. However, when I open a .csv file it considers it 1940.

View Replies!   View Related
12 Digit Number Into Date/time

is there a way to convert many entries of dates and time entered in the above way into actual dates and times? is it as simple as making a custom format? if so, whats the syntax?

the entries appear to be the year(4 characters), the month (2 characters), the day of month (2 characters), and the time of day (6 characters, AM/PM)

i dont need the time, so the last part ("120000[0:GMT]") isn't necessary.

View Replies!   View Related
Sorting Two Digit Groups Within A Cell
I have a column of Cells.
Each cell has five, two digit number pairs with spaces between the pairs.
IE: 25 03 48 19 33

How can I get those number sets to sort numerically on the first digit, and still maintain the two digit grouping?

View Replies!   View Related
Statistical Analysis Of Three Digit Numbers
using Excel 2007, windowsXP, and am trying to find the right formula for the following problem.

I have a column of 3 digit numbers, and want to determine how many times a number occurs in the first position ie: 123 the second position ie: 123 and the third position ie: 123 in the entire column of figures?

View Replies!   View Related
How Do I Get The Dashes To Not Count As A Digit/number?
I have this script that does what i need it to do but one thing, I need the script to count the numbers as digits only and not the dashes in the format.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("I21:I24")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If (Len(Target) > 11) Or (Len(Target) < 10) Then
MsgBox "Not a valid number of characters, please try again"
Target.Value = vbNullString...................

View Replies!   View Related
Generate A Random 9 Digit Number
I have a spreadsheet with approx 11,000 rows and I would like to generate a unique 9 digit number for each line.

I know I could just put 100 000 000 and then increase that by one all the way down but I was wondering if there was any formula or code

View Replies!   View Related
Adding A Digit Into Column Of 2 Sheets
i am working in office. my boss has given me 68 work sheets to analyze. i have completed all the task except one. i want to add a capital D to the left of numbers into column B of 68 worksheets. excel sheet is attached. it is only sample. i have more than 6000 record in each work sheet. but how can i do this in only one click.

View Replies!   View Related
Extract 5 Digit Number From String
I need to extract a set of 5 numbers from a string of characters. The number set I need is always 5 digits long, always starts with a 1, and always begins after a period. But the number I need can occur in different parts of the string:

In each example below I would want to extract only the 10572 portion.


Additionally (if possible) if the cell is blank I don't want anything to show up, and if the string begins with a letter then I don't want anything to show up either.

View Replies!   View Related
Display All 8 Digit Numbers That Have The Digits 1-8 In Them
I need a way to display all 8 digit numbers that have the digits 1-8 in them. (ie. 12345678 but NOT 12345679 OR 12345677) Also If I could somehow divide by 13 then check for whole numbers

View Replies!   View Related
Convert The Digit In Specific Format
I work in the flooring industry and the format we use for cuts of carpet/vinyl is 40-3 (for 40.25 feet), etc. So we are trying to create an excel spreadsheet that will add up these cuts. Right now it's kind of a mess because we have decimals in a column we will be hiding and are converting that to our industry format.

The formula I am having trouble with is as follows:

The problem is that whenever the totals end in zero, the formula won't convert 40.00 to 40-0. It gives me an error every time. If the total is 40.25 it will convert it to 40-3 just fine.

View Replies!   View Related
Return Last Digit In Cell As A Number
I am trying to get a formula to return the last digit from a number in a cell as a number.

If the cell (B3) contains a number such as 164 or 34 I want to return a value of 4 and have it be formated as a number.

I have tried =RIGHT(B3,1) and it returns a 4 but I cannot get another formula to recognize it as a number.
It shows up in the cell on the left side as text would and if I format it as a number nothing happens it is still on the left and the formula does not work.

View Replies!   View Related
Remove Underscores From Each Of These 3 Digit Numbers
I have a spreadsheet with 1000's of 3 digit numbers and each one has a underscore in front of it.

HOw can I remove the underscore from each of these 3 digit numbers without doing a find/replace on each different number?

View Replies!   View Related
Searching Through A String For Position Of 1st Non-Zero Digit
I have a text string in a cell, something like:


I need to find a way to find the first nonzero character in the string, and what position it is in. For instance, in the above example, 2 is the first non-zero character, so the function would return 5, since the 2 is the fifth character into the string.

View Replies!   View Related
Remove Last Digit On A Series Of Numbers
I have a spreadsheet, with about 1,500 lines. In column A is a list of numbers that I need to remove the last digit from each number, for example in A22 is 02602726521 - I need this to be 0260272652. The numbers are all uniqe.

How can I remove the last digit from every number, without going in manually to do so?

View Replies!   View Related
Counting Pairs From 4 Digit Lottery
I am tring to list and count how many pairs in a 4 digit lottery?

no duplicates ...

View Replies!   View Related
Formulating Dates And 16 Digit Entries
I have a list of names and numbers when I add new entries I place them in alpha order. I need to insert extra lines because I have first and last name in the field. Also, how do you formulate cells for dates and also to accept a 16 digit number.

View Replies!   View Related
Double Digit Number 03 Vs 3 With Formula
="autECLSession.autECLPS.SendKeys """&B4*100&""""

B4 is 3.00% and I need it to be 03 no %, 3.00%*100= 3 but I need anything less then 10 to be two digits still (1-9)

This would be the perfect end result:
autECLSession.autECLPS.SendKeys "03"

and for over 10

autECLSession.autECLPS.SendKeys "10"

View Replies!   View Related
Convert X Digit Number To Letters
Nice to meet you all. I'd be grateful for any help I could get on this as I have tried it and I'm a bit stumped...

What I need to do is the following:

Convert a 4 digit number (e.g. 1234) in a single cell to a 4 letter string (e.g BCDE) and have the output appear in another cell.

The conversion should be as follows:


So, for example, 3678 in one cell should be converted to DGHI in the target cell.

View Replies!   View Related
Get Day Of Week As Number From 6 Digit String
In my macro, I have a variable 'logDate' that is a 6 digit date in the format mmddyy

If 'logDate' falls on a Monday then I need to set another variable 'd' to equal 1, if Tuesday then 2 etc.

ie: 'logdate' = 062308 I need to set 'd' =

View Replies!   View Related
Convert 4 Digit Numbers To Times
Is there any way changing general four digit number into hours and minutes?

I have almost 31000 rows of imported data, showing arrival and departure times with four digits (dep 2130, arr 0130) without colons and I need to subtract dep from arr to get block hours. Is there a way to add colon to multiple cells or some other solution to my problem?

Adding colon to 62000 cells one by one does not sound that good to me.

View Replies!   View Related
Transfer 8-digit Number To Date
I have an 8-digit number like this :


06 = year 2006
152 = day number 152 in the year
002 = sequence of entry

Is it possiable to transfer that number into date in one cell and the sequence of entry in another cell ?

View Replies!   View Related
Increment Last Digit In Text Cells
I've got a column of IP addresses and want to increment the last octet. Starting with IP block in column A, I select those and drag to auto-fill to the right. Some cells increment and others don't. I'm using Excel 2003 on Windows XP.

View Replies!   View Related
Ensure TextBox Only Contains X Digit Number
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("B3").address
End Sub

I would like to change the target to ceratin cells in my active row. How would I write the code to achieve this.

View Replies!   View Related
Add Area Codes To 7 Digit Phone Numbers
I have a large list of phone numbers & some of the numbers do not have area codes. The area code is the same for all numbers. Is there a way to add the area codes to all of these numbers without keying all in manually

View Replies!   View Related
Remove The First Digit From A Numeric And Replace With Remainder
I have a list of stock codes which are 6 digits long and there are over 550 of them.
We have shortened our codes to a five digit number, removing the first digit.
Is there a way to delete the first digit of the numeric and replace it with the remainder in all of the 550+ cells?

View Replies!   View Related
Copyright 2005-08, All rights reserved