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.

Questions:

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

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?

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

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've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....

I received a request from a coworker regarding custom formatting some numbers in his spreadsheet. Those numbers are serial numbers of 20 characters long. Sometimes in my files I use this custom number formatting ###0 and its enough for the data I handle. But when I tried to use it in his spreadsheet, the following shows:

8456891070060510000

The cell must look like this: 08456891070060510302

The reason to have it like this is due to a Delivery Program requirement to deliver Set-top Units for repair. The Delivery Program do not recognize other format than the above. My coworker takes the data from a spreadsheet, and the spreadsheet needs a custom number format to display the correct number.

find a custom number format to be able to display as my coworker need it??

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

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.

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

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

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.

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

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.

I'm trying to search a column that has cells with various length strings. Included in the strange (at random locations) could be possibly be a 7 digit number starting with 4. I want a formula that counts the number of cells with contain that 7 digit number. Is this possible?

I have a cell containing body of an email.How can i check for 15 or 16 digit number in the text and extract it. Basically i am checking for a credit card number in the email and if it is there then i need to extract it to adjacent colum.

Image below has a 500 cells I want to delete the 12 digit number but the numbers that align to the 12 digit was in one cell so it is hard to do manually how to delete it

I am using following formulae to convert 16 digit number to with validation that number is not exceeding 16 digits and should always start with "1" else it should give Error with 11 blank spaces

but when I input any number which has a value other than zero e.g. 1009510010000059 its returning result as 1009510010000050 I need validation also in the same formulae

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

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.

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.

I have two Columns of 4 digit numbers (24 hour clock format). I need these numbers to be in Time Format. Formatting the Columns or even individual cells seems to do nothing.

1238 as 12:38 etc.

These numbers are acquired using VLOOKUP

I have attached a copy of my file, The numbers in question are on sheet "iCalender" 8th sheet accross. Columns "start Time" and "End Time"

Time Format is Compulsory so that I can upload to a .ics Web Converter. Of which is currently failing for this reason.