How to extract number from formula in a cell?

[Ed from AZ]

In XL 2007, I have a column of cells with a formula like
"=C2-1234.5". I'm building another set of formulas for another data
table elsewhere on the sheet, and I need to extract the number from
the other formula.

A twist: sometimes the formula has been added to alng the lines of
"=C2-1234.5-789.5". I need to extract the first number only.

Is there an easy way to make this happen?

Ed

 

[RagDyer]

Use Edit - Replace!

Works on formulas too.

 

[Rick Rothstein \(MVP - VB\)]

Assuming the number you need to extract is the 1234.5 and not the 2 from the
C2, give this a try...

=LEFT(MID(A1,FIND("-",A1)+1,99),FIND("-",MID(A1,FIND("-",A1)+1,99)&"-")-1)

Rick

 

[Ed from AZ]

Hi, Rick. I kept getting a VALUE error. It looks like FIND examines
the cell value, not the formula in the cell.

In A1, I put 10.
In B1, I put =A1-3.5, and got a cell value of 6.5.
In C1, I put = FIND("-",B1), and got a VALUE error.
In D1, I put =FIND("3", B1), and got a VALUE error.
In E1, I put = FIND("6", B1), and got a cell value of 1.

How do I process the formula as a string, rather than the displayed
value?

Ed

 

[Ron Rosenfeld]

In XL 2007, I have a column of cells with a formula like
"=C2-1234.5". I'm building another set of formulas for another data
table elsewhere on the sheet, and I need to extract the number from
the other formula.

A twist: sometimes the formula has been added to alng the lines of
"=C2-1234.5-789.5". I need to extract the first number only.

Is there an easy way to make this happen?

Ed

What do you mean by "extract the number"?

Do you want to remove the number from the formula?

Or do you want to use that same number in another formula?

Either way, you are going to have to use VBA, I believe, in order to process
the formula as a string.

This VBA UDF will return the first number (constant value) in the formula
located in rg.

If there is no number in the formula, then the function will return a #VALUE!
error.

If this is not what you want, you will need to be more specific.

To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explore window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter the function

=FirstConstInFormula(cell_ref)

into some cell where cell_ref refers to the cell containing the formula.

=================================
Option Explicit
Function FirstConstInFormula(rg As Range) As Double
Dim re As Object, mc As Object
Dim str As String
str = rg.Formula
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\d*\.?\d+\b"
re.Global = False
If re.test(str) = True Then
Set mc = re.Execute(str)
FirstConstInFormula = mc(0).Value
Else
FirstConstInFormula = ""
End If
End Function
=====================================
--ron

 

[RagDyer]

Is the number you're trying to remove from your formula different in every
row?

Like:

=C2-1235.5
=C3-246.8

OR, is it the same in every formula?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Hi, Rick. I kept getting a VALUE error. It looks like FIND examines
the cell value, not the formula in the cell.

In A1, I put 10.
In B1, I put =A1-3.5, and got a cell value of 6.5.
In C1, I put = FIND("-",B1), and got a VALUE error.
In D1, I put =FIND("3", B1), and got a VALUE error.
In E1, I put = FIND("6", B1), and got a cell value of 1.

How do I process the formula as a string, rather than the displayed
value?

Ed

 

[Klemen25]

With my limited knowledge- I did this by puting ' before = of the
formula. This changes formula to text string and you can edit it as a
string.
If this works for you somehow than search if there is some macro that
would add and than remove the ' sign tho ease this process.
Good luck
(and if you find this macro you can post it here)

 

[Ed from AZ]

A bit more searching found that I'm not the only one to have this
question over the years. The answer turned out to be a UDF to return
the formula in a cell as a text string:

Function CellFormula(Rng As Range) As String
CellFormula = Rng.Formula
End Function

This will let me use LEFT, RIGHT, MID and other functions to find the
number in the formula and extract to use in a MATCH or other formula.

I wish Excel would have this as a native worksheet function - some of
my co-workers have no clue what to do with a macro alert!!

Thanks to all who responded.

Ed

 

[Rick Rothstein \(MVP - VB\)]

As long as you have to drop down to the VB level, you might as well do all
the extraction work down there. Ron already gave you a UDF that will return
the number directly. Here is my previously posted formula reworked into a
UDF to do the same...

Function GetNumber(R As Range) As Variant
GetNumber = Left(Mid(R.Formula, InStr(R.Formula, "-") + 1), InStr(Mid( _
R.Formula, InStr(R.Formula, "-") + 1) & "-", "-") - 1)
End Function

Rick


A bit more searching found that I'm not the only one to have this
question over the years. The answer turned out to be a UDF to return
the formula in a cell as a text string:

Function CellFormula(Rng As Range) As String
CellFormula = Rng.Formula
End Function

This will let me use LEFT, RIGHT, MID and other functions to find the
number in the formula and extract to use in a MATCH or other formula.

I wish Excel would have this as a native worksheet function - some of
my co-workers have no clue what to do with a macro alert!!

Thanks to all who responded.

Ed

 

[RagDyeR]

This can be accomplished *without* the use of a UDF if you use a relative
"named formula".

Post back if you're interested.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

A bit more searching found that I'm not the only one to have this
question over the years. The answer turned out to be a UDF to return
the formula in a cell as a text string:

Function CellFormula(Rng As Range) As String
CellFormula = Rng.Formula
End Function

This will let me use LEFT, RIGHT, MID and other functions to find the
number in the formula and extract to use in a MATCH or other formula.

I wish Excel would have this as a native worksheet function - some of
my co-workers have no clue what to do with a macro alert!!

Thanks to all who responded.

Ed

 

[Ed from AZ]

This can be accomplished *without* the use of a UDF if you use a relative
"named formula".

Post back if you're interested.
--

Regards,

RD

Yes, indeed - I would be ~very~ interested!!

Ed

 

[Ed from AZ]

Yah - makes a lot of sense! Which is probably why I missed it!!

I changed "As Variant" to "As Single", and your UDF works perfect!

Thanks, Rick, for a great bit of help.

Ed

 

[RagDyer]

First , would just like to clarify, exactly what do you want to happen to
these formulas of yours with your use of the word "extract".

Original
=C2-1234.5
What final outcome do you want returned?
=C2
OR
-1234.5

Original
=C2-1234.5-789.5
What final outcome do you want returned?
=C2-789.5
OR
-1234.5


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

This can be accomplished *without* the use of a UDF if you use a relative
"named formula".

Post back if you're interested.
--

Regards,

RD

Yes, indeed - I would be ~very~ interested!!

Ed

 

[Ed from AZ]

In both instances, just the first number 1234.5 - no cell reference,
no signs.

Ed

 

[Ron Rosenfeld]

In both instances, just the first number 1234.5 - no cell reference,
no signs.

Ed

Just curious if there was a problem with my suggestion from yesterday?
--ron

 

[Ed from AZ]

To be honest, Ron, I didn't try your VBA UDF. Right when you posted
it, Rick gave me a worksheet function, which is what I would rather
try. But a bit more searching of the newsgroups suggested that I
wasn't going to get away from a VBA UDF, and the one-liner I
originally found was more understandable and less intimidating at my
level of non-expertise than yours. (I've touched RegExp before, but
that's about it.)

And if RagDyer presents a non-VBA UDF solution, then I'll abandon VBA
altogether for this project. I use macros everywhere and have no
problems with them (except trying to remember what an older macro was
supposed to do!), but some of my co-workers who try to use my
"special" workbooks are very intimidated by a macro or a button.

Ed

 

[RagDyeR]

The first priority here is to be able to convert a working XL formula into a
form that can be parsed.
This is easily accomplished by incorporating an old XL 4.0 macro into a
*relative* named formula.

We'll configure this named formula to reference the column just to the left
of any cell containing this formula, though it can be configured to work on
*any* cell that's relative to the location of the formula containing cell.

Click in B1.
Doesn't matter if it's empty or populated.
From the Menu Bar:
<Insert. <Name> <Define>

In the "Names In Workbook" box, enter a short name for this formula,
let's use
ext
to denote extract.

Then, change whatever's in the "Refers To" box to:

=Get.Cell(6,A1)

Then <OK>

With a *relative* reference to A1 (no $ signs), and since you clicked in B1
at the start, this formula will work on any cell to the immediate left of
*any* cell that contains this formula.

So, say in D5 you entered:
Cat123
In E5 enter
=ext
and you'll get "cat123" returned.

Now, in D2 enter the formula:
=C2-1234.5
and in E2 enter:
=ext
And you'll get the formula returned, *not* the results of the formula.


Now, to use a Text formula to parse the formula itself.

If all the formulas that you're *ever* going to parse,
will simply have a single negative (=C2-1234.5), or a double negative
(=C2-1234.5-789.5),
then you can just use Rick's formula in the column to the right of the
column containing your formulas:

=LEFT(MID(ext,FIND("-",ext)+1,99),FIND("-",MID(ext,FIND("-",ext)+1,99)&"-")-1)

I got carried away a little, trying to put together a Text formula that
would return the *first negative* number,
no matter what else was in the formula.
So, a formula like this:
=C2+144.1-12.3+J6-250.1
would have a return of "12.3".

=IF(ISERR(FIND("-",ext)),"",LOOKUP(99^99,--MID(MID(ext,FIND("-",ext)+1,10),
MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(ext,FIND("-",ext)+1,10)&"0123456789")),ROW($1:$10))))

It probably could be shorter, but I'm off to the plant now.

If you like this procedure, don't forget that you can configure the named
formula to work on cells in *any* relative position compared to the location
of the formula containing cell.

And a final caveat.
In XL02 and later, there are no restrictions to the use and copying of these
XL 4.0 macro formulas.
In earlier versions, they should *not* be copied between workbooks,
but they can be copied between sheets within the same WB.
Copying between work*books* in earlier versions, prior to XL02, will crash
XL.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


In both instances, just the first number 1234.5 - no cell reference,
no signs.

Ed

 

[Ron Rosenfeld]

To be honest, Ron, I didn't try your VBA UDF. Right when you posted
it, Rick gave me a worksheet function, which is what I would rather
try. But a bit more searching of the newsgroups suggested that I
wasn't going to get away from a VBA UDF, and the one-liner I
originally found was more understandable and less intimidating at my
level of non-expertise than yours. (I've touched RegExp before, but
that's about it.)

And if RagDyer presents a non-VBA UDF solution, then I'll abandon VBA
altogether for this project. I use macros everywhere and have no
problems with them (except trying to remember what an older macro was
supposed to do!), but some of my co-workers who try to use my
"special" workbooks are very intimidated by a macro or a button.

Ed

Thanks for posting that, Ed.
--ron