Formulae created from text strings will not compute.

[Guest]

In anExcel 2000 workbook I have created a series of formulae in a column
using a text string. To create the text string I used a different formula
initially to pull together a mixture of text and the contents of cells in
another column. (There is a very good reason for this.) I then copy and
special paste the "text strings" in the new column as values. At this point
all the cells appear to be formulae (using cells from another sheet in the
same workbook), but they do not compute - not until I click on each
individual cell with my mouse and press return. THEN the newly created
formula in one cell computes, and returns the answer. I can only perform this
task for one cell at a time: I can't get the whole column of newly created
formulae to compute all at once.

If you understood this, you're a real brain box, but is there a solution to
getting all the column to compute at once?

 

[Guest]

as i understood your practical question..
i test run one sample
on column A, format cell into text.
i type =1+1 > cell appears as =1+1
then i copy Column A> paste special> values to column B
I select whole column B
Click Edit>Find>
Find =
Replace =
replace all
then see result as 2 on all cells of column B.

logistics is the name not the brainbox.

 

[Pete_UK]

You could create a User Defined Function along the lines of:

Function Eval(Rng As Range) As Variant
Application.Volatile True
Eval = Evaluate(Rng.Text)
End Function

Then, call this as =Eval(A2) in cell B2, where the text strings
representing the formula are in column A. Copy this formula down column
B and it will return the result of the "formulae" in column A.

Hope this helps.

Pete

 

[Lori]

Another approach: select column and then Data > Text to Columns >
Finish.

 

[Guest]

You're not a brain box: you're a genius!

I did what you said, and, hey presto! It's so simple!
It works on my formulae too.

Thanks
Bart

 

[Guest]

Hi, Lori.
Yes, your approach also works!
It's as if the formulae need that extra "push" to complete the computation.
Don't understand why, though.

Thanks again,
Bart

 

[Guest]

I ought to get into this; but, since the two other suggestions worked,
that'll do me fine!
I gues your solution would be useful in a macro file.

Cheers,
Bart