convert string to a formula

[doug firr]

I have a column, actually of screen resolutions with thousands of rows that look something like this:

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]943*530[/TD]
[/TR]
[TR]
[TD="class: xl65"]953*572[/TD]
[/TR]
[TR]
[TD="class: xl65"]969*768[/TD]
[/TR]
[TR]
[TD="class: xl65"]986*1236[/TD]
[/TR]
[TR]
[TD="class: xl65"]999*599[/TD]
[/TR]
</tbody>[/TABLE]

I would like to know the screen area hence add "=" in front of each value.

I did this using concatenate but it just displays the forumla (e.g. "=943*530) and does not in fact do any calculation.

How do I get around this to show the calculated values?

 

[VoG]

Try

=LEFT(A1,FIND("*",A1)-1)*RIGHT(A1,LEN(A1)-FIND("*",A1))

 

[davesweep]

Hi,

Assuming your data is in column A, the following macro will do as you require

Sub a()
Dim rCell As Range

For Each rCell In Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
rCell.Offset(0, 1).Formula = "=" & rCell.Value
Next

End Sub

 

[doug firr]

Thanks VoG. That works but I was hoping for a means of just converting the string to the formula, something like "=toformula". I encounter this frequently and wondered if anyone knew of anything along those lines

 

[facethegod]

Thanks VoG. That works but I was hoping for a means of just converting the string to the formula, something like "=toformula". I encounter this frequently and wondered if anyone knew of anything along those lines

You can do this in 2 steps

Assuming data starts in A1

1) In the adjacent cell put ="^"&A1, drag down to end of data range.

2) Highlight the entire column then type Ctrl+F (Find & Replace window): >Find what: ^ >Replace with: = >Replace All.

Hope this helps!