Hi 'mama,
If the numbers are "all Zip codes," there is a very strict 'rhyme and reason' to them within the bounds of Wayne's question:
Zip codes ALL have exactly five digits. Zip+ codes ALL have exactly nine digits, plus a separator between the five digit Zip part and the four digit extension..
Here's a table with a formula that will give you the sum of the digits in any five digit number:
The formula, entered in cell B2, then filled down into the rest of the cells in column B, is shown below:
B2: SUM(MID(A2,1,1),MID(A2,2,1),MID(A2,3,1),MID(A2,4,1),MID(A2,5,1))
The formula is constructed from two functions, SUM and MID.The syntax is shown below:
SUM(value,value…)
value in this case is a number. value… indicates one or more similar values may be added to the list, each separated from the one before by a comma.
MID(source-string,start-position,number-of-characters)
The source-string is the string of characters in the Zip code in column A.
The starting position is a number telling Numbers which character to grab from the string. This number is the only change in the five MID statements in the formula. Each MID statement starts one character further along in the string from the one before.
number of characters is 1—each MID statement extracts a single digit from the Zip number.
For ten character Zip+ codes, you will need four more MID statements, with start-positions of 7, 8, 9, and 10.
I'll assume ALL of your Zip codes are the five digit variety, as that keeps the formula simple. If you do need to sume the longer variety, let me know in a reply, and I'll post a longer formula that can handle both types.
Regards,
Barry
PS: The first four examples are made up (although they could exist); the last three are real.
B.
