string 如何将字符串公式转换为“真实”公式

How to turn a string formula into a "real" formula

提问by Cloaky

I have 0,4*A1in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?

我0,4*A1在一个单元格中（作为一个字符串）。如何将此“字符串公式”转换为真正的公式并在另一个单元格中计算其值？

采纳答案by Fionnuala

Evaluatemight suit:

Evaluate可能适合：

http://www.mrexcel.com/forum/showthread.php?t=62067

http://www.mrexcel.com/forum/showthread.php?t=62067

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

回答by Robbie

I Concatenated my formula as normal but at the start I had '=instead of =.

我像往常一样连接了我的公式，但一开始我'=用的是=.

Then I copy and paste as text to where I need it. Then I highlight the section saved as text and press ctrl+ Hto find and replace.
I replace '=with =and all of my functions are active.

然后我将文本复制并粘贴到我需要的地方。然后我突出显示保存为文本的部分并按ctrl+H查找和替换。
我替换'=为=并且我的所有功能都处于活动状态。

Its a few stages but it avoids VBA.

它有几个阶段，但它避免了 VBA。

I hope this helps,

我希望这有帮助，

Rob

抢

回答by Patrick Honorez

Just for fun, I found an interesting article here, to use a somehow hidden evaluate function that does exist in Excel. The trick is to assign it to a name, and use the name in your cells, because EVALUATE() would give you an error msg if used directly in a cell. I tried and it works! You can use it with a relative name, if you want to copy accross rows if a sheet.

只是为了好玩，我在这里找到了一篇有趣的文章，以使用 Excel 中确实存在的某种隐藏的评估函数。诀窍是将它分配给一个名称，并在您的单元格中使用该名称，因为如果直接在单元格中使用 EVALUATE() 会给您一个错误消息。我试过了，它有效！如果要跨行复制工作表，则可以将它与相对名称一起使用。

回答by wardw123

UPDATEThis used to work (in 2007, I believe), but does not in Excel 2013.

更新这曾经有效（我相信在 2007 年），但在 Excel 2013 中无效。

EXCEL 2013:

EXCEL 2013：

This isn't quite the same, but if it's possible to put 0.4 in one cell (B1, say), and the text value A1 in another cell (C1, say), in cell D1, you can use =B1*INDIRECT(C1), which results in the calculation of 0.4 * A1's value.

这并不完全相同，但如果可以将 0.4 放在一个单元格（例如 B1）中，并将文本值 A1 放在另一个单元格（例如 C1）中，在单元格 D1 中，您可以使用 =B1*INDIRECT( C1)，这导致计算 0.4 * A1 的值。

So, if A1 = 10, you'd get 0.4*10 = 4in cell D1. I'll update again if I can find a better 2013 solution, and sorry the Microsoft destroyed the original functionality of INDIRECT!

因此，如果 A1 = 10，您将进入0.4*10 = 4单元格 D1。如果我能找到更好的 2013 解决方案，我会再次更新，抱歉微软破坏了 INDIRECT 的原始功能！

EXCEL 2007 version:

EXCEL 2007版：

For a non-VBA solution, use the INDIRECTformula. It takes a string as an argument and converts it to a cell reference.

对于非 VBA 解决方案，请使用INDIRECT公式。它将字符串作为参数并将其转换为单元格引用。

For example, =0.4*INDIRECT("A1")will return the value of 0.4 * the value that's in cell A1 of that worksheet.

例如，=0.4*INDIRECT("A1")将返回值 0.4 * 该工作表的单元格 A1 中的值。

If cell A1 was, say, 10, then =0.4*INDIRECT("A1")would return 4.

如果单元格 A1 是 10，那么=0.4*INDIRECT("A1")将返回 4。

回答by DrMarbuse

I prefer the VBA-solution for professional solutions.

我更喜欢专业解决方案的 VBA 解决方案。

With the replace-procedure part in the question search and replace WHOLE WORDS ONLY, I use the following VBA-procedure:

使用问题搜索中的替换过程部分并替换整个 单词，我使用以下 VBA 过程：

''
' Evaluate Formula-Text in Excel
'
Function wm_Eval(myFormula As String, ParamArray variablesAndValues() As Variant) As Variant
    Dim i As Long

    '
    ' replace strings by values
    '
    For i = LBound(variablesAndValues) To UBound(variablesAndValues) Step 2
        myFormula = RegExpReplaceWord(myFormula, variablesAndValues(i), variablesAndValues(i + 1))
    Next

    '
    ' internationalisation
    '
    myFormula = Replace(myFormula, Application.ThousandsSeparator, "")
    myFormula = Replace(myFormula, Application.DecimalSeparator, ".")
    myFormula = Replace(myFormula, Application.International(xlListSeparator), ",")

    '
    ' return value
    '
    wm_Eval = Application.Evaluate(myFormula)
End Function


''
' Replace Whole Word
'
' Purpose   : replace [strFind] with [strReplace] in [strSource]
' Comment   : [strFind] can be plain text or a regexp pattern;
'             all occurences of [strFind] are replaced
Public Function RegExpReplaceWord(ByVal strSource As String, _
ByVal strFind As String, _
ByVal strReplace As String) As String

    ' early binding requires reference to Microsoft VBScript
    ' Regular Expressions:
    ' with late binding, no reference needed:
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")

    re.Global = True
    're.IgnoreCase = True ' <-- case insensitve
    re.Pattern = "\b" & strFind & "\b"
    RegExpReplaceWord = re.Replace(strSource, strReplace)
    Set re = Nothing
End Function

Usage of the procedure in an excel sheet looks like:

在 Excel 工作表中使用该过程如下所示：

回答by mountainclimber

In my opinion the best solutions is in this link: http://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function

在我看来，最好的解决方案是在这个链接中：http: //www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function

Here is a summary: 1) In cell A1 enter 1, 2) In cell A2 enter 2, 3) In cell A3 enter +, 4) Create a named range, with "=Evaluate(A1 & A3 & A2)" in the refers to field while creating the named range. Lets call this named range "testEval", 5) In cell A4 enter =testEval,

这里是一个总结：1) 在单元格 A1 中输入 1, 2) 在单元格 A2 中输入 2, 3) 在单元格 A3 中输入 +, 4) 创建一个命名范围，在“=Evaluate(A1 & A3 & A2)”中在创建命名范围时引用字段。让我们称这个命名范围为“testEval”，5）在单元格 A4 中输入 =testEval，

Cell A4 should have the value 3 in it.

单元格 A4 中的值应为 3。

Notes: a) Requires no programming/vba. b) I did this in Excel 2013 and it works.

注意：a) 不需要编程/vba。b) 我在 Excel 2013 中做了这个并且它有效。

回答by user6698332

Say, let we have column Efilled by formulas that returns string, like:

说，让我们E用返回字符串的公式填充列，例如：

= " = " & D7

where D7cell consist more complicated formula, that composes final desired result, say:

其中D7单元格包含更复杂的公式，组成最终所需的结果，例如：

= 3.02 * 1024 * 1024 * 1024

And so in all huge qty of rows that are.

所以在所有大量的行中。

When rows are a little - it just enough to copy desired cells as values (by RMB)
to nearest column, say G, and press F2with following Enterin each of rows.
However, in case of huge qty of rows it's impossible ...

当行有点少时 - 只需将所需的单元格作为值（按人民币）复制
到最近的列，例如G，然后按F2并在每一行中输入以下Enter。
但是，在大量行的情况下，这是不可能的......

So, No VBA. No extra formulas. No F&R

所以，没有VBA。没有额外的公式。没有 F&R

No mistakes, no typo, but stupid mechanical actions instead only,

没有错误，没有错别字，只有愚蠢的机械动作，

Like on a Ford conveyor. And in just a few seconds only:

就像在福特传送带上一样。只需几秒钟：

1. [Assume, all of involved columns are in "General" format.]
2. Open Notepad++
3. Select entire column D
4. Ctrl+C
5. Ctrl+Vin NPP
6. Ctrl+Ain NPP
7. Select cell in the first row of desired column G1
8. Ctrl+V
9. Enjoy :) .

1. [假设，所有涉及的列都是“常规”格式。]
2. 打开记事本++
3. 选择整列 D
4. Ctrl+C
5. NPP 中的Ctrl+V
6. NPP 中的Ctrl+A
7. 选择所需列的第一行中的单元格 G1
8. Ctrl+V
9. 享受 ：） 。

回答by JakobW

The best, non-VBA, way to do this is using the TEXT formula. It takes a string as an argument and converts it to a value.

最好的非 VBA 方法是使用 TEXT 公式。它接受一个字符串作为参数并将其转换为一个值。

For example, =TEXT ("0.4*A1",'##') will return the value of 0.4 * the value that's in cell A1 of that worksheet.

例如，=TEXT ("0.4*A1",'##') 将返回值 0.4 * 该工作表的单元格 A1 中的值。