Replace string with condensed version?

posty

New Member
Joined
Oct 17, 2006
Messages
23
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,931
Office Version
  1. 365
Platform
  1. Windows
Do you want exactly =1715 in the cell or just the value 1715?
 

posty

New Member
Joined
Oct 17, 2006
Messages
23
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Select the cells and try:

Code:
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

New Member
Joined
Oct 17, 2006
Messages
23
Select the cells and try:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,402
Messages
5,547,748
Members
410,810
Latest member
thepinkbird
Top