Excel：在“kx + m”中查找k和m文本字符串 [英] Excel: Find k and m in "kx + m" text string

问题描述

使用 VBA 或公式在 kx + m string 中找到k和m code>？

Is there a clever way using VBA or a formula to find "k" and "m" variables in a kx+m string?

kx + m字符串可以看起来有几种情况，例如：

There are several scenarios for how the kx+m string can look, e.g.:

312*x+12
12+x*2
-4-x

等等。我很确定我可以通过在Excel中编写非常复杂的公式来解决这个问题，但我想也许有人已经解决了这个和类似的问题。这是我迄今为止最好的拍摄，但它并不处理所有情况（比如当kx + m字符串中有两个minuses时：

and so on. I'm pretty sure I can solve this by writing very complicating formulas in Excel, but I'm thinking maybe someone has already solved this and similar problems. Here is my best shot so far, but it doesn't handle all situations yet (like when there are two minuses in the kx+m string:

= TRIM（IF（NOT（ISERROR（SEARCH（〜+; F5）））;
IF（SEARCH（〜+; F5）> SEARCH（〜*; F5）; RIGHT（F5; LEN（F5）-SEARCH（〜+; F5））; LEFT（F5; SEARCH（〜+; F5）-1））;
IF（NOT（ISERROR （〜）F5）））;
IF（SEARCH（〜; F5）> SEARCH（〜*; F5）; RIGHT（F5; LEN（F5） - ; F5）+1）; LEFT（F5; SEARCH（〜*; F5）-1））;）））

推荐答案

我确定这将有助于您：）

I'm sure this will help you :)

将此功能放在模块中：

Function FindKXPlusM(ByVal str As String) As String
    Dim K As String, M As String
    Dim regex As Object, matches As Object, sm As Object

    '' remove unwanted spaces from input string (if any)
    str = Replace(str, " ", "")

    '' create an instance of RegEx object.
    '' I'm using late binding here, but you can use early binding too.
    Set regex = CreateObject("VBScript.RegExp")
    regex.IgnoreCase = True
    regex.Global = True

    '' test for kx+m or xk+m types
    regex.Pattern = "^(-?\d*)\*?x([\+-]?\d+)?$|^x\*(-?\d+)([\+-]?\d+)?$"
    Set matches = regex.Execute(str)
    If matches.Count >= 1 Then
        Set sm = matches(0).SubMatches
        K = sm(0)
        M = sm(1)
        If K = "" Then K = sm(2)
        If M = "" Then M = sm(3)
        If K = "-" Or K = "+" Or K = "" Then K = K & "1"
        If M = "" Then M = "0"
    Else
        '' test for m+kx or m+xk types
        regex.Pattern = "^(-?\d+)[\+-]x\*([\+-]?\d+)$|^(-?\d+)([\+-]\d*)\*?x$"
        Set matches = regex.Execute(str)
        If matches.Count >= 1 Then
            Set sm = matches(0).SubMatches
            M = sm(0)
            K = sm(1)
            If M = "" Then M = sm(2)
            If K = "" Then K = sm(3)
            If K = "-" Or K = "+" Or K = "" Then K = K & "1"
            If M = "" Then M = "0"
        End If
    End If
    K = Replace(K, "+", "")
    M = Replace(M, "+", "")

    '' the values found are in K & M.
    '' I output here in this format only for showing sample.
    FindKXPlusM = " K = " & K & "         M = " & M
End Function

然后你可以从宏
调用它像这样：

Then you can either call it from a Macro e.g. like this:

Sub Test()
    Debug.Print FindKXPlusM("x*312+12")
End Sub

或者像公式一样使用它。
例如将它放在单元格中：

Or use it like a formula. e.g. by putting this in a cell:

=FindKXPlusM(B1)

我喜欢第二种方式（较少的工作：P）

I like the second way (less work :P)

我用各种值进行了测试，截图我获得的是：

I tested it with various values and here's a screenshot of what I get:

希望这有助于：）

这篇关于Excel：在“kx + m”中查找k和m文本字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！