Concatenation of a Formula

[Tiziano]

I have a bunch of formulas in Column D. All these formulas are incorrectly
written, so I would like to remedy that by pre-pending/appending two text
strings to them; this would create new, correct formulas in the process.

For the sake of simplicity, let's assume that in Cell D1 I have this
original formula:
=A1*B1+C1

To this formula, I would like to pre-pend the string "=if(E1>5," and append
the string ",A1*B1+(C1*4))", thus obtaining the new formula:
=if(E1>5,A1*B1+C1,A1*B1+(C1*4))

Unfortunately, I cannot simply revise the formula in D1 and then copy/paste
down Column D because not all the formulas in that column are the same...
However, all the original formulas need to have the exact same strings
pre-pended and appended.

I tried with the =CONCATENATE() function and with the ampersand ("&"), but
nothing works because Excel does not regard the original formulas as text
strings. (I would get rid of the "=" sign in the original formula by also
embedding the functions =LEFT() and =LEN() in the formula.)

Can it be done?

Thanks.

 

[Dave Peterson]

The prefix and suffix never vary???

If that's true, you could use a macro. Select your range (all of column D???)
and run this:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Formula = "=if(e1>5," _
& Mid(myCell.Formula, 2) _
& ",A1*B1+(C1*4))"
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Tiziano]

Hi, Dave.
Thanks for the suggestion.
I now realize that I wasn't clear enough... What I meant to say is that the
prefix/suffix never vary in terms of formula type, however they change in
terms of row address.

So, as a simple example, the complete and new formula in D2 would be
something like this:
=if(E2>5,A2*B2+C2+F2,A2*B2+(C2*4))

I am not sure that I will be able to run a macro due to high security level
settings. Is there a way to solve my problem via a formula?

Thanks.

 

[T. Valko]

If you can't use a macro to do this then it's gonna be a real kludge!

Luckily, I'm pretty good at kludge! <g>

Let's assume you have this formula in D1:

=A1*B1+C1

You want to add these expressions to either end:

IF(E1>5,
,A1*B1+(C1*4))

So the new formula is:

=IF(E1>5,A1*B1+C1,A1*B1+(C1*4))

Enter the expressions you want to add in 2 cells:

K1 = IF(E1>5,
L1 = ,A1*B1+(C1*4))

Create this named formula:
Insert>Name>Define
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-2]",FALSE))
OK

Are you familiar with R1C1 referencing? "RC[-2]" refers to the cell in the
current row 2 columns to the left. So, if we enter that formula in cell F1
"RC[-2]" refers to cell D1.

Enter this formula in F1:

="="&K$1&MID(Formula,2,255)&L$1

The result of that formula will be a TEXT string that looks like the formula
you want:

=IF(E1>5,A1*B1+C1,A1*B1+(C1*4))

Now, to convert that text string to a real working formula:

Copy cell F1
Then, Edit>Paste special>Values>OK
Then, Edit>Replace
Find what: =
Replace with: =
Replace all

Now you have the formula you wanted. Make sure things look ok then you can
delete your old formulas and move the new formulas to the old location.

--
Biff
Microsoft Excel MVP

Hi, Dave.
Thanks for the suggestion.
I now realize that I wasn't clear enough... What I meant to say is that
the prefix/suffix never vary in terms of formula type, however they change
in terms of row address.

So, as a simple example, the complete and new formula in D2 would be
something like this:
=if(E2>5,A2*B2+C2+F2,A2*B2+(C2*4))

I am not sure that I will be able to run a macro due to high security
level settings. Is there a way to solve my problem via a formula?

Thanks.

 

[Tiziano]

Thank you Dave and Biff for your suggestions.