Concatenation of a Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tiziano - 24 Aug 2007 02:49 GMT

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.

Signature

Reply to this Message

Dave Peterson - 24 Aug 2007 02:59 GMT

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

> 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
[quoted text clipped - 23 lines]
> --
> tb

Signature

Dave Peterson

Reply to this Message

Tiziano - 24 Aug 2007 04:54 GMT

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.

Signature

> The prefix and suffix never vary???
>
[quoted text clipped - 61 lines]
>> --
>> tb

Reply to this Message

T. Valko - 24 Aug 2007 06:51 GMT

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.

Signature

Biff
Microsoft Excel MVP

> Hi, Dave.
> Thanks for the suggestion.
[quoted text clipped - 78 lines]
>>> --
>>> tb

Reply to this Message

Tiziano - 29 Aug 2007 01:52 GMT

Thank you Dave and Biff for your suggestions.

Signature

>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
[quoted text clipped - 21 lines]
>
> Thanks.

Reply to this Message

Rate this thread: