# Convert Excel formulas containing functions to a simple form

##### New Member
Hello Everyone,

I am a beginner with VBA. I want write a macro to convert a formula which contains functions to a simple form. e.g. D12+SUM(E22:E26) should be converted to D12+E22-E26 or D12+SUM(E22:E26)-SUM(F12:F15)-D20+SUM(G12:G14) should be converted to D12+E22+E23+E24+E25+E26-F12+F13+F14+F15-D20+G12+G13+G14.

I have been trying a lot but no success......Can someone please help me out.......or if someone has already done such sort of thing?

Any help would be highly appreciated.

Thanks

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Here are some possibilities for you.

1) First, you have to parse the existing formula. I assume you know that you can get to the formula in a cell using the Range.Formula property. Assign that to a text value and chop off the equal sign. Next, scan through for the operators + and -. (I'm assuming there is no multiplication or division in any of this?) Load everything between those operators into an array of strings, and the operators into a different array. (Assume the first operator is positive.)

So for example:

=SUM(E6:E10)+F10-SUM(D10:D14)

This would yield two arrays:

{SUM(E6:E10),F10,SUM(D10:D14)}
{+,+,-}

2) Now, you want to look through that first array to expand it out, if necessary. I'm just going to discuss expanding SUM, but it would work similarly if you have other functions you need to expand.

First, search the string to see if the term SUM is found. If not, assume it cannot be decomposed, and move on to the next string in the array.

If you did find it, then get rid of the SUM( at the beginning and the ) at the end, and define a variable of type Range using the text left over as the range.

Now, use a For Each/Next statement to iterate through each cell in that range. For that cell, use the Range.Address property to get the text of that cell address. You can specify whether you want to use relative or absolute cell reference.

Start assembling a new string based on the operator from the second array (the + or -) and each cell reference returned. You would wind up with "+E6+E7+E8+E9+E10", for example.

3) Once you've done that for all of the elements of your array, simply concatenate them together. Make sure that no operators get duplicated in this, but if you're careful it ought to work.

Hi Shawn,

Thanks for your suggestions. I will give it a try and then get back to you guys.

Anyone else here with other ideas?

Replies
6
Views
616
Replies
13
Views
358
Replies
1
Views
158
Replies
5
Views
352
Replies
2
Views
316

1,216,496
Messages
6,130,985
Members
449,612
Latest member
geniusufo007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back