Solved Sum cells with three numbers in each cell seperated by a /

July 14, 2012 at 04:30:39
Specs: Windows 7
Hello. I need to sum cells with three different numbers in the each cell. The numbers are seperated by a /. For example A1 = 4/3/1 and A2 = 3/2/1. I need cell A3 to be 7/5/2. Please help.

See More: Sum cells with three numbers in each cell seperated by a /

Report •


✔ Best Answer
July 14, 2012 at 09:37:38
You could try this Array formula. It must be entered using Ctrl-Shift-Enter and there can not be any cells that don't contain values formatted like in your example.

Blanks cells or cells that don't contain xx/yyy/z will cause a #VALUE error.

Once you enter it using Ctrl-Shift-Enter brackets { } will appear around it

=(SUM(LEFT(A1:A40,FIND("/",A1:A40)-1)*1)&"/")&
SUM(MID(A1:A40,FIND("/",A1:A40)+1,
FIND("/",A1:A40,FIND("/",A1:A40)+1)-
(FIND("/",A1:A40)+1))*1)&"/"&
SUM(RIGHT(A1:A40,LEN(A1:A40)-
FIND("/",A1:A40,(FIND("/",A1:A40)+1)))*1)

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



#1
July 14, 2012 at 06:19:46
The easiest way would be to use the Data - Text To Columns feature and separate the numbers into their own cells (i.e. A1, B1, C1) and then use a formula like this:

=SUM(A1:A2)&"/"&SUM(B1:B2)&"/"&SUM(C1:C2)

If the numbers must remain in A1 and A2, then this will work for your example since you only have single digit numbers:

=SUM(LEFT(A1,1),LEFT(A2,1))&"/"
&SUM(MID(A1,3,1),MID(A2,3,1))&"/"
&SUM(RIGHT(A1,1),RIGHT(A2,1))

Once you start getting into variable lengths of numbers things get a bit more complicated, e.g.

        A
1   34/213/1 
2   1/43/266

Now you have to introduce the FIND function to the locate the slashes and then extract the numbers between them.

Just to get the first SUM (35/) from those numbers, you'd need:

=SUM(LEFT(A1,FIND("/",A1)-1),LEFT(A2,FIND("/",A2)-1))&"/"

Extracting the middle digits requires an even more complicated combination of functions.

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


Report •

#2
July 14, 2012 at 07:36:27
I will need to use larger numbers (2 and 3 digits). Are you able to provide me with a complete formula to your example? Thanks for your help!

Report •

#3
July 14, 2012 at 08:07:58
I also woud like to sum multiple (40) cells wth 3 number in each cell. Is there an easier way to do this than multiple formuals in a single cell?

For example:

1 34/213/1
2 1/43/266
3 23/3/33
4 25/4/56
...(40)


Report •

Related Solutions

#4
July 14, 2012 at 08:58:50
Are you not willing to use the Data - Text To Columns feature, which will make all of this a lot simpler?

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


Report •

#5
July 14, 2012 at 09:37:38
✔ Best Answer
You could try this Array formula. It must be entered using Ctrl-Shift-Enter and there can not be any cells that don't contain values formatted like in your example.

Blanks cells or cells that don't contain xx/yyy/z will cause a #VALUE error.

Once you enter it using Ctrl-Shift-Enter brackets { } will appear around it

=(SUM(LEFT(A1:A40,FIND("/",A1:A40)-1)*1)&"/")&
SUM(MID(A1:A40,FIND("/",A1:A40)+1,
FIND("/",A1:A40,FIND("/",A1:A40)+1)-
(FIND("/",A1:A40)+1))*1)&"/"&
SUM(RIGHT(A1:A40,LEN(A1:A40)-
FIND("/",A1:A40,(FIND("/",A1:A40)+1)))*1)

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


Report •

#6
July 14, 2012 at 11:10:14
That works perfectly. I was beginig to think it was nearly impossile to do what I was asking.

I didn't want to change the spreadsheet with text to columns because it is not my spreadsheet to change. I just want to recommend an easier way of adding up the rows without a calculator.

Thanks for you help!!


Report •


Ask Question