Replace string with condensed version?

[posty]

I have this spreadsheet that has many entries in a cell similar to this:

=503+86+103+76+96+117+67+96+103+56+100+127+81+104

What I would like would be a macro (or something) that would go through any range that I request (say B5:b19) and replace each string like the above and replace it with the sum total. So it would yield in this cell...

=1715

There is a total of 9 columns that I would like to this to and many many columns, but there is data in between some of the rows that I would not want to touch.

Is this possible?

TIA

 

[Norie]

Do you want exactly =1715 in the cell or just the value 1715?

 

[posty]

Do you want exactly =1715 in the cell or just the value 1715?

I would want the "=1715" so if and when things are added in the future, the addition would still occur. I just want to shrink the very large string to something manageable...

 

[Andrew Poulsom]

Select the cells and try:

Sub Test()
    Dim Cell As Range
    On Error Resume Next
    For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
        Cell.Formula = "=" & Cell.Value
    Next Cell
End Sub

 

[posty]

Select the cells and try:

Sub Test()
    Dim Cell As Range
    On Error Resume Next
    For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
        Cell.Formula = "=" & Cell.Value
    Next Cell
End Sub

Perfect! Thanks.