Convert trendline equations into formulas in Excel easily

We've all been there, you've created a graph in excel, needed to smooth or extrapolate the data so have resorted to doing a quick and dirty trendline.

1

2

=trendlineparse("y = 2.6948ln(x) - 0.4039 ",2)

Public Function TrendlineParse(Eqtn As String, Optional xVal As Variant = "eq", Optional xType As Variant = 0) As Variant
    'Function Convert trendline equations into formulas
    ' (c) Martin Childs 2017

    Eqtn = Replace(Eqtn, " ", "")

    'Return equation
    If xVal = "eq" Then
        TrendlineParse = Eqtn
        Exit Function
    End If

    'Logarithmic
    If InStr(Eqtn, "ln(") > 0 Then
        TrendlineParse = Val(Mid(Eqtn, 3, InStr(Eqtn, "l") - 3)) * Log(xVal) + Val(Mid(Eqtn, InStr(Eqtn, ")") + 1, Len(Eqtn) - InStr(Eqtn, ")")))
        Exit Function
    End If

    'Exponential
    If InStr(Eqtn, "e") > 0 Then
        TrendlineParse = Val(Mid(Eqtn, 3, InStr(Eqtn, "e") - 3)) * Exp(Val(Mid(Eqtn, InStr(Eqtn, "e") + 1, Len(Eqtn) - 1 - InStr(Eqtn, "e")) * xVal))
        Exit Function
    End If

    'Get x positions
    x1 = InStr(Eqtn, "x")
    x2 = InStr(x1 + 1, Eqtn, "x")
    x3 = InStr(x2 + 1, Eqtn, "x")
    x4 = InStr(x3 + 1, Eqtn, "x")
    x5 = InStr(x4 + 1, Eqtn, "x")
    x6 = InStr(x5 + 1, Eqtn, "x")

    'Power
    If LCase(xType) = "power" Or LCase(xType) = "pow" Or xType = "p" Then
                TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, Len(Eqtn) - 1 - x1))
        Exit Function
    End If

    'Poly1
    If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 1 Then
        TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal + _
                        Val(Mid(Eqtn, x1 + 1, Len(Eqtn) - 1 - x1))
        Exit Function
    End If
    
    'Poly2
    If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 2 Then
        TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _
                        Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal + _
                        Val(Mid(Eqtn, x2 + 1, Len(Eqtn)))
        Exit Function
    End If
    
    'Poly3
    If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 3 Then
        TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _
                        Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal ^ Val(Mid(Eqtn, x2 + 1, 1)) + _
                        Val(Mid(Eqtn, x2 + 2, x3 - x2 - 2)) * xVal + _
                        Val(Mid(Eqtn, x3 + 1, Len(Eqtn)))
        Exit Function
    End If
    
    'Poly4
    If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 4 Then
        TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _
                        Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal ^ Val(Mid(Eqtn, x2 + 1, 1)) + _
                        Val(Mid(Eqtn, x2 + 2, x3 - x2 - 2)) * xVal ^ Val(Mid(Eqtn, x3 + 1, 1)) + _
                        Val(Mid(Eqtn, x3 + 2, x4 - x3 - 2)) * xVal + _
                        Val(Mid(Eqtn, x4 + 1, Len(Eqtn)))
        Exit Function
    End If
    
    'Poly5
    If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 5 Then
        TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _
                        Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal ^ Val(Mid(Eqtn, x2 + 1, 1)) + _
                        Val(Mid(Eqtn, x2 + 2, x3 - x2 - 2)) * xVal ^ Val(Mid(Eqtn, x3 + 1, 1)) + _
                        Val(Mid(Eqtn, x3 + 2, x4 - x3 - 2)) * xVal ^ Val(Mid(Eqtn, x4 + 1, 1)) + _
                        Val(Mid(Eqtn, x4 + 2, x5 - x4 - 2)) * xVal + _
                        Val(Mid(Eqtn, x5 + 1, Len(Eqtn)))
        Exit Function
    End If
    
    'Poly6
    If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 6 Then
        TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _
                        Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal ^ Val(Mid(Eqtn, x2 + 1, 1)) + _
                        Val(Mid(Eqtn, x2 + 2, x3 - x2 - 2)) * xVal ^ Val(Mid(Eqtn, x3 + 1, 1)) + _
                        Val(Mid(Eqtn, x3 + 2, x4 - x3 - 2)) * xVal ^ Val(Mid(Eqtn, x4 + 1, 1)) + _
                        Val(Mid(Eqtn, x4 + 2, x5 - x4 - 2)) * xVal ^ Val(Mid(Eqtn, x5 + 1, 1)) + _
                        Val(Mid(Eqtn, x5 + 2, x6 - x5 - 2)) * xVal + _
                        Val(Mid(Eqtn, x6 + 1, Len(Eqtn)))
        Exit Function
    End If
    

End Function

You can download the spreadhseet here

 TrendlineParse.xlsm