# 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 /

✔ 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/2663 23/3/334 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 •