Splitting numbers of a cell and adding them together in an ArrayFormula on Google Sheets

My question is how can I have digits of a cell added together in an array formula format, in that

``````12 | -> 3      //(result: 3 = 1 + 2)
14 | -> 5
46 | -> 10
11 | -> 2
53 | -> 8
``````

And let’s just say the values 12, 14, 46 on are on column A and the answered would be B.

• so the result will be 18? (3+5+10) ? – user0 Jan 4 at 22:53
• no, i am looking for a way to make 12 into 3 by making 12->1+2=3, via an array formula. The only thing that has worked for me is =REGEXREPLACE(A1:A,"\D+", "") however, that is only in a single row instance - changing it into =ArrayFormula(REGEXREPLACE(A1:A,"\D+", "")) does not calculate all values in the A column for me, but rather only the first one – user205439 Jan 4 at 23:00

• up to 2 digits:

``````=ARRAYFORMULA(IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
+ RIGHT (A1:A, 1), ))``````

• up to 3 digits:

``````=ARRAYFORMULA(IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
+ RIGHT (A1:A, 1),
IF(LEN(A1:A)=3, LEFT  (A1:A, 1)
+ MID   (A1:A, 2, 1)
+ RIGHT (A1:A, 1), )))``````

• up to 4 digits:

``````=ARRAYFORMULA(IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
+ RIGHT (A1:A, 1),
IF(LEN(A1:A)=3, LEFT  (A1:A, 1)
+ MID   (A1:A, 2, 1)
+ RIGHT (A1:A, 1),
IF(LEN(A1:A)=4, MID   (A1:A, 1, 1)
+ MID   (A1:A, 2, 1)
+ MID   (A1:A, 3, 1)
+ MID   (A1:A, 4, 1), ))))``````

``````=ARRAYFORMULA(IF(LEN(A1:A)=1,        A1:A,
IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
+ RIGHT (A1:A, 1),
IF(LEN(A1:A)=3, LEFT  (A1:A, 1)
+ MID   (A1:A, 2, 1)
+ RIGHT (A1:A, 1),
IF(LEN(A1:A)=4, MID   (A1:A, 1, 1)
+ MID   (A1:A, 2, 1)
+ MID   (A1:A, 3, 1)
+ MID   (A1:A, 4, 1), )))))``````

• limit array for only first 3 ocurances:

``````=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(LEN(A1:A)=1,        A1:A,
IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
+ RIGHT (A1:A, 1),
IF(LEN(A1:A)=3, LEFT  (A1:A, 1)
+ MID   (A1:A, 2, 1)
+ RIGHT (A1:A, 1),
IF(LEN(A1:A)=4, MID   (A1:A, 1, 1)
+ MID   (A1:A, 2, 1)
+ MID   (A1:A, 3, 1)
+ MID   (A1:A, 4, 1), ))))), 3, 1)``````