### 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```