如何在Excel中对单个单元格内的四位数字进行求和及拆分求和？

Let’s break down both of your requested calculations with simple, practical Excel formulas—no complicated tricks needed!

1. Add the First Two Digits to the Last Two Digits (e.g., 1234 → 12 + 34 = 46) #

Assuming your 4-digit number is in cell A1, use this formula:

=LEFT(A1,2)+RIGHT(A1,2)

• LEFT(A1,2) pulls the first two characters (digits) from the left side of the cell
• RIGHT(A1,2) grabs the last two characters from the right
• The + operator automatically converts these text strings into numbers, so Excel can calculate their sum without extra steps

This works whether your cell is formatted as plain text or a number—Excel handles the conversion in the background.

2. Add Up Each Individual Digit Separately (e.g., 1234 → 1 + 2 + 3 + 4 = 10) #

You’ve got two solid options here, depending on your preference:

Option 1: Direct Digit Extraction & Sum #

Use the MID function to pull each digit one by one, then add them up:

=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)

• MID(A1,1,1) extracts the 1st digit (starts at position 1, takes 1 character)
• MID(A1,2,1) gets the 2nd digit, and so on for positions 3 and 4
• Just like before, the + operator turns each text digit into a numeric value for summation

Option 2: Scalable SUMPRODUCT Approach #

If you might need to adjust the number of digits later (say, from 4 to 5), this formula is more flexible:

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:4")),1))

• ROW(INDIRECT("1:4")) generates a sequence of numbers 1 through 4 (one for each digit position)
• MID(A1,ROW(...),1) extracts each digit in order
• The double hyphen (--) converts the extracted text digits into numbers
• SUMPRODUCT sums all the converted digits in one step

内容的提问来源于stack exchange，提问作者Boby Marley