Extract 1st number in formula

[pianonet]

I have a row of formulas, and would like to sum the first number in the formulas. The formulas are in this format: a/b*1000, where a and b are not the same across the row.


Any clues? Thanks for any and all help!


Pianonet


ps there is an error in the word in "Tags (comma seperated)" -- s/b spelled separated

 

[SirJB7]

Hi, pianonet!

Help me clarifying your problem: you have formulas in a row, with the structure =a/b*1000, with no additional parenthesis nor other symbols, where "a" are constants (values) or single cell references and you want to get the sum of all the "a" values en all the cells of that row. Is that right?

Regards!

 

[Luke M]

If you're wanting info about an actual formula, you'll need to use some VB to get the formula, as Excel has not native functions that can read formulas.

If that's the case, here's the UDF that I use:

[pre]

Function GetFormula(r As Range, Optional x As Boolean = False) As String
If r.Count > 1 Then GetFormula = "#VALUE!"
'Check if formula returned in R1C1 style
If x Then
GetFormula = r.FormulaR1C1
Else
GetFormula = r.Formula
End If
End Function

[/pre]
If it's the formula inside a cell you want to look at, your formula will be:

=VALUE(LEFT(A2,FIND("/",A2)-1))


If it is the formula inside a cell, install the UDF, then use this:

=VALUE(MID(GetFormula(A2),2,FIND("/",GetFormula(A2))-2))