Substitute a "/" for a "+" and add numbers

[jeffreybrown]

If have a cell with 13/11/4

How can I add these numbers?

I've tried =LEFT(D7,FIND("/",D7)-1)+MID(D7,FIND("/",D7)+1,1)+RIGHT(D7,FIND("/",D7)-2)

but can't get the mid part to find the 11; instead, it only finds the 1 which makes the answer 18 but should be 28.

Is there a way to use =SUBSTITUTE(D7,"/","+") and then sum.

Can I get some further direction and help here?

 

[gsistek]

I would use

=LEFT(A1,FIND("/",A1)-1)+MID(A1,FIND("/",A1)+1,FIND("/",SUBSTITUTE(A1,"/","*",1))-FIND("/",A1)-1)+RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1,"/","*",1)))

 

[Krishnakumar]

Hi,

There may be better ways..

I assume your data is in A1.

Select A1

Define SumString

Refers to: =EVALUATE("{"&SUBSTITUTE(Sheet1!$A1,"/",",")&"}")

In B1:

=SUM(SumString)

 

[pgc01]

Another option using Kris' method.

Select A1, define the name SumString as :

=EVALUATE(SUBSTITUTE(Sheet1!$A1,"/","+"))

In B1:

=SumString

 

[jeffreybrown]

Thanks to all, all formula's work great. Seems PGC's method would be easisest (at least for me) to remember.

 

[Haseeb Avarakkan]

Hello Jeff, Try also,

=SUMPRODUCT(--MID(SUBSTITUTE("/"&A1,"/",REPT(" ",50)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))+1))*50,49))

 

[T. Valko]

Thanks to all, all formula's work great. Seems PGC's method would be easisest (at least for me) to remember.

Just be aware that using the EVALUATE method, the formula MUST be entered in a very specific cell.

 

[jeffreybrown]

Hi Haseeb...this works great also.

Hi Biff...Yes I was kinda of wonding that since the Evaluate is pointing to a specific cell. It seems from what I can tell, it only works looking at the cell immediately to the left. Is this right?

 

[T. Valko]

Hi Biff...Yes I was kinda of wonding that since the Evaluate is pointing to a specific cell. It seems from what I can tell, it only works looking at the cell immediately to the left. Is this right?

Yes, that's correct.

 

[jeffreybrown]

Thanks