2

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
0
  • 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), ))))

  • added single digit support:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.