Adding multiple numbers contained within a single cell

[babycody]

Is it possible to have multiple numbers i.e. 2,43,8 in a single cell, and show the sum of these numbers in another cell? Also is it possible to add characters IIII together to get 4? Thanks ahead for any help on this matter.

 

[Andrew Poulsom]

For the first question put this function in a General module:

Function Eval(Ref)
    Eval = Evaluate(CStr(Ref))
End Function

Then you can use:

=Eval(SUBSTITUTE(A1,",","+"))

where A1 contains the list of comma separated numbers.

 

[Joe4]

Also is it possible to add characters IIII together to get 4?

You really can't add "I"s since they are chacacters, not numbers, but you can count them using the LEN function:
=LEN(A1)

 

[babycody]

Very Nice on both replies. Works like a charm. Thanks very much for your help.

 

[babycody]

OK, one more question on the Eval formula. When the cell A1 is blank the formula returns ####. How can I clean this up?

 

[Joe4]

Try this modification to Andrew's code:

Function Eval(Ref)
    If Len(Ref) > 0 Then Eval = Evaluate(CStr(Ref))
End Function

 

[babycody]

Not quite the problem I have. The LEN() function is working properly. It is used in a completely seperate column. The VBA code is where I am having the problem. Is there something like an error trap that I can use on the formulas? I really just need the Eval function to return nothing if the cell to the left is blank.

 

[Andrew Poulsom]

Try this modification to Andrew's code:

Function Eval(Ref)
    If Len(Ref) > 0 Then Eval = Evaluate(CStr(Ref))
End Function

Or:

=IF(LEN(A1),Eval(SUBSTITUTE(A1,",","+")),"")

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> eval(ref)
<SPAN style="color:#00007F">If</SPAN> (Len(ref)) <SPAN style="color:#00007F">Then</SPAN>
    eval = Evaluate(Application.WorksheetFunction.Substitute(ref, ",", "+"))
<SPAN style="color:#00007F">Else</SPAN>
    eval = vbNullString
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[babycody]

Thanks Andrew Poulsom. The adjustment to the formula itself you gave me worked very nicely. Thanks for everyones input on this.