Solved How to get the total of numbers within a cell.

January 30, 2012 at 12:15:33
Specs: Windows XP
Hi,
I was wondering if anyone can help me, I would like to total up several figures which are in a cell and get the total on the C1 cell skipping B1 column, example:

A1
15879

C1
30
Your help will be highly appreciated.
Thanks
Lexy


See More: How to get the total of numbers within a cell.

Report •

#1
January 30, 2012 at 13:29:13
Put this in C1:

=SUM(LEFT(A1,1),MID(A1,2,1),MID(A1,3,1),MID(A1,4,1),RIGHT(A1,1))

Unfortunately, it's only going to work for a 5 digit string of numbers.

There is no built in Excel function to loop through a string of characters in a cell and SUM them.

If your string will be of varying lengths, you'll need to use VBA.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 30, 2012 at 14:03:28
Hi,

Thanks for your quick response, some numbers I need totals for are 6 digit string of numbers within a cell, how do I use the VBA code.

Thanks:)



Report •

#3
January 30, 2012 at 18:52:28
✔ Best Answer
This formula will work for 5 or 6 digits:

=IF(LEN(A1)=5,SUM(LEFT(A1,1),MID(A1,2,1),MID(A1,3,1),MID(A1,4,1),RIGHT(A1,1)),SUM(LEFT(A1,1),MID(A1,2,1),MID(A1,3,1),MID(A1,4,1),MID(A1,5,1),RIGHT(A1,1)))

As written, it will give erroneous answer for string greater than 6 characters but that could be fixed if need be.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 31, 2012 at 00:10:24
Thanks a million it worked like MAGIC :)))

Report •

Ask Question