Pasting cells' value as formula

[Hall]

I have a column where each cell has a formula that constructs a string. How
do I copy/paste this to an adjacent column such that the string acts as a
formula in the new column's cells?

For a simplified example, column B has the formulas
=concatenate("=text(",a1,")")
=concatenate("=text(",b1,")")
=concatenate("=text(",c1,")")
etc

So that column B's values show
=text(123)
=text(456)
=text(789)
etc

How do I copy/paste column B to column C so that column C shows the values
of these literal formulas
123 (as text, in this example)
456
789
etc

 

[Bernard Liengme]

The simple answer is =A1, =B1,=C1
But I assume there is more to this!
Try =MID(B1,7,LEN(B1)-7)

 

[Hall]

Bernard

You took my example too literally. I tried to make it a simple example of
the mechanics that I need not the context.

The formulas are much more complicated than this.

 

[Bucky]

I have a column where each cell has a formula that constructs a string. How
do I copy/paste this to an adjacent column such that the string acts as a
formula in the new column's cells?

I think what you're looking for is a way to evaluate a string as a
formula. You need to use VBA to do this. There was a similar question
asked a few days ago:

http://groups.google.com/group/micr..._frm/thread/35d2a43561b4382e/dd4cd4255ef9bdc9

 

[Guest]

Using VBA you can easily convert a set of strings into a single string (thru
conctenation). Convert the single string into a formula; and convert the
formula into a value.

In A1 thru A4 enter:
=
7
+
9
all as strings (format the cells as text prior to entering).

In B1 enter:
=CONCATENATE(A1,A2,A3,A4)

then enter and run this macro:

Sub Macro2()
Range("B1").Copy
Range("C1").PasteSpecial Paste:=xlPasteValues
j = Range("C1").Value
Range("D1").Value = j
j = Range("D1").Value
Range("E1").Value = j
End Sub

B1 will show =7+9 in the cell and the concatenate formula in in formula bar.

C1 will show =7+9 in both the cell and formula bar because its just a string.

D1 will show 16 in the cell and =7+9 in the formula bar because its a formula.

E1 will show 16 in both the cell and formula bar because its a value.


You don't need any of the intermediate stuff, so use:

Sub Macro1()
Range("B1").Copy
Range("C1").PasteSpecial Paste:=xlPasteValues
j = Range("C1").Value
Range("C1").Value = j
j = Range("C1").Value
Range("C1").Value = j
End Sub