how to extarct data from within the formula written

[nehaa1234]

hi all,
I have a query
if in a cell I write the formula =12000-2000 so I get the answer 10000
my question is how do I extract the amount " 2000" (which is used in the formula) in another cell

note: im not writing 12000 or 2000 in any column . im just using a single cell to write the formula.

 

[Hui]

Nehaa1234

Firstly, Welcome to the Chandoo.org Forums

The quick answer is you can't

If you want/need to be able to you could enter it as text
ie: '=12000-2000
Note the leading '

I'd suggest entering the numbers into separate cells and you can then process them as required elsewhere

 

[Khalid NGO]

Hi,

If you have 2013 or greater, you can use the formulatext:

=MID(FORMULATEXT(A1),FIND("-",FORMULATEXT(A1))+1,99)
where A1 = 12000-2000

Regards,

 

[Hui]

And I've learned a new function today !

 

[John Jairo V]

Hi @nehaa1234, @Hui and @Khalid NGO

In any version of Excel, using an UDF:

Function ExtNum(R As Range)
   ExtNum = 1 * Split(R.Formula, "-")(1)
End Function

And later, you can use this like ExtNum(A1), if A1 is the cell with the formulae.

Blessings to all!

 

[Asheesh]

@Khalid NGO though i use this function often...but never thought out of box...AMAZING..

 

[Eloise T]

Hi @nehaa1234, @Hui and @Khalid NGO

In any version of Excel, using an UDF:

Function ExtNum(R As Range)
   ExtNum = 1 * Split(R.Formula, "-")(1)
End Function

And later, you can use this like ExtNum(A1), if A1 is the cell with the formulae.

Blessings to all!

How do you engage a UDF vs. a VBA?
I know Alt+F11, F7, paste VBA, then F5 to run.
How does UDF work?

 

[Marc L]

Hi !

You can call a personal function (or User Defined Function) only if :

• code is located in a standard module (not a class one like worksheet)

• code doesn't change any cell but the cell calling it …
​

 

[Eloise T]

Isn't a VBA essentially a User Defined Function?
I noticed that VBAs start with "Sub" and UDFs apparently start with "Function."
For a VBA: Alt+F11, F7, paste VBA, then F5 to run.
Compared to a VBA, how do you initiate a UDF?

 

[bobhc]

@Elosie T
By calling the UDF, in this case "ExtNum". VBA/Macro/UDF all VBA to diffrent limits

 

[Eloise T]

You initiate a UDF by calling the UDF (ExtNum), ...and after that you lost me.

 

[Marc L]

Once function is created in a VBA standard module,
you can use it within a cell formula :​

 

[Nebu]

Hi:

Here is another way to extract the formula text with out VBA, for versions below 2013

Supposing your formula is in column A, define a named range, say Test =GET.CELL(6,!$A1) and use the following formula in B1
=RIGHT(Test,LEN(Test)-FIND("-",Test))

Here is a sample file attached.

Thanks

 

[nehaa1234]

thank you all. we can also use text to column function and using "-" as delimiter

 

[Eloise T]

Once function is created in a VBA standard module,
you can use it within a cell formula :​

View attachment 26815

What application did you use to capture and replay the video?

 

[Hui]

I use Camtasia Studio and make an Animated GIF

 

[Eloise T]

Thank you.

 

[Marc L]

It depends on the computer I am but between free Gif screen generators :
LICEcap, ScreenToGif, …

Camtasia Studio is not free but can create a presentation with voice.
​

 

[Eloise T]

It depends on the computer I am but between free Gif screen generators :
LICEcap, ScreenToGif, …

Camtasia Studio is not free but can create a presentation with voice.
​

Thank you. I'll give them both a try.