In Excel there is a function called Sum numbers in a cell that allows you to add up all the individual numbers within a cell.
In A1 I have a date that I want to be added up like 1+9+7+6 = 23
At the moment the only way I can do this is with single numbers per cell like Row 3, but this is a very clumsy way to work.
MacBook Pro 13″, macOS 13.0
If they're all 4-digit "dates" then you can do something like this:
=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)
Use ; instead of , in the formula if your region uses , as a decimal separator.
SG
If they're all 4-digit "dates" then you can do something like this:
=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)
Use ; instead of , in the formula if your region uses , as a decimal separator.
SG
Here is a way that will let you sum them regardless of the number of digits, except there are none. You can put IFERROR around it with the answer of 0 to catch that possibility.
=COUNTIF(REGEX.EXTRACT(A1,"\d",0),"1")+2×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"2")+3×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"3")+4×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"4")+5×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"5")+6×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"6")+7×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"7")+8×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"8")+9×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"9")
If you don't see the entire formula above, click and hold on the "=" and drag down until the entire thing (even the stuff outside of the window) is eelected. Then copy/past to your table.
REGEX.EXTRACT output is text. I wish we had a SUM function that would sum text "numbers" but we don't. That would have made it a lot easier and shorter.
Thanks SG,
That's amazing!
What if I don't know how many characters there will be? So in the example it was just a year, but what if I have a longer number and I'm not sure how many numbers there are? Is there a way to automate this process?
I haven't found a neat way to automate it but if you think you may have, say, up to 7 digits, then you can do something like this:
=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)+IF(MID(A1,5,1)="",0,MID(A1,5,1))+IF(MID(A1,6,1)="",0,MID(A1,6,1))+IF(MID(A1,7,1)="",0,MID(A1,7,1))
Repeated here in case the forum mangled it:
=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)+IF(MID(A1,5,1)="",0,MID(A1,5,1))+IF(MID(A1,6,1)="",0,MID(A1,6,1))+IF(MID(A1,7,1)="",0,MID(A1,7,1))
Just extend that if you think you might have even more digits. It will still work with fewer-digit numbers too.
SG
Or:
=COUNTMATCHES(A2,1)×1+COUNTMATCHES(A2,2)×2+COUNTMATCHES(A2,3)×3+COUNTMATCHES(A2,4)×4+COUNTMATCHES(A2,5)×5+COUNTMATCHES(A2,6)×6+COUNTMATCHES(A2,7)×7+COUNTMATCHES(A2,8)×8+COUNTMATCHES(A2,9)×9
SG
Even better.
Wow! Amazing! Thank you!
Sum numbers in a cell