=LEFT(TEXT(A1;"000");1)+RIGHT(TEXT(A1;"000");1)+RIGHT(LEFT(TEXT(A1;"000");2);1)
=SUMPRODUCT(VALUE(MID(A1;ROW(OFFSET(A1;0;0;LEN(A1);1));1)))
| 1 | 2 | 3 |
| 123 |
Kacem wrote:If there is formula to add numbers in the same cell. Example cell B1 258 formula that could bring me the total 15
Assuming that your cell is A1, all of these work for me based on my understanding of the situation:Kacem wrote:It will be 3 numbers single digit, any numbers from 1....9.
=A1-INT(A1/100)*9-INT(A1/10)*9where the green formula must be entered as an Array formula.
=A1-9*SUMPRODUCT(INT(A1/{10;100}))
=LEFT(TEXT(A1;"000");1)+RIGHT(TEXT(A1;"000");1)+RIGHT(LEFT(TEXT(A1;"000");2);1) ← from Zizi64
=SUM(VALUE(MID(A1;ROW($A$1:$A$3);1))) ← from karolus
=SUMPRODUCT(VALUE(MID(A1;ROW(OFFSET(A1;0;0;LEN(A1);1));1))) ← from Lupp
"didn't work" is not helpful in the forum because it tells us what did not happen. Please never use that phrase in a post. We need to know exactly what actions you took, what did happen, and what you expected to happen. Attaching a document demonstrating the problem is almost always helpful for us and will get your problem solved more quickly. If you typed a formula, pressed Enter, and got #VALUE!, tell us that. Then attach your document so we can see what's wrong. Often "it didn't work" means "it didn't do what I expected" and the real problem is that the program is behaving correctly but your expectations are incorrect. To help you solve the problem we need to know your expectations and your ultimate goal.Kacem wrote:thanks zizi64 for your answer didn't work.
=INT(MOD($A3/100000;10))+INT(MOD($A3/10000;10))+INT(MOD($A3/1000;10))+INT(MOD($A3/100;10))+INT(MOD($A3/10;10))+INT(MOD($A3;10))
=INT(MOD($A4/100000;10))+INT(MOD($A4/10000;10))+INT(MOD($A4/1000;10))+INT(MOD($A4/100;10))+INT(MOD($A4/10;10))+INT(MOD($A4;10))
1101943 has seven digits so you need seven terms:CaliRay for B4 wrote:=INT(MOD($A4/100000;10))+INT(MOD($A4/10000;10))+INT(MOD($A4/1000;10))
+INT(MOD($A4/100;10))+INT(MOD($A4/10;10))+INT(MOD($A4;10))
The problem is, I need a formula that will add the numbers in a cell together individually when the number of digits varies.
REM ***** BASIC *****
Option Explicit
Function AddNumbers(iTheNumber as long) as Integer
Dim iSum as long
Dim iLenght as integer
Dim i as integer
Dim iOneNumber as integer
Dim sTheNumber as string
Dim sOneChar as string
sTheNumber = Str(Abs(iTheNumber)) 'Supress the "-" sign when the input is a negative number
iLenght = Len(sTheNumber)
iSum = 0
For i = 2 to iLenght ' from 2: Because the converted string has one plus character for the "+" sign, but the "+" is not appeared, it is a whitespace)
sOneChar = Mid(sTheNumber,i,1)
iOneNumber = Val(sOneChar)
iSum = iSum + iOneNumber
Next i
AddNumbers = iSum
end function
robleyd wrote:This seems to work based on Lupp's response above; adapt it as you need.
Zizi64 wrote:Try to write your own macro function for this task. You can convert the passed integer number to string format, then you can enumerate the number of the characters. Then a loop (FOR-NEXT or DO-WHILE) can get each characters one-by-one from the text, and it can convert them back to numetic value, finally it will add them.
While this won't help in this specific case (because the number of digits keeps changing)
robleyd wrote:While this won't help in this specific case (because the number of digits keeps changing)
You may not have noticed, but the formula is the same for each row and is capable of handling varying length numbers. I simply entered a number of rows with different length numbers by way of example to demonstrate the capabilities of the formula.. Try, for example, changing A1 to a different number of digits and see what happens.
Users browsing this forum: No registered users and 17 guests