Evaluating text as formulae

Is it possible to refer to text in a cell as though it were a formula, ie 
execute it instead of displaying it?  Eg if the result of your formula was 
"A1 + B2", to actually add A1 and B2 and display the result?

Thanks

Nigel
0
10/12/2005 3:33:43 PM
excel 39879 articles. 2 followers. Follow

3 Replies
412 Views

Similar Articles

[PageSpeed] 14

Like concatenating text?
=concatenate(a1," + ",b2)
or
=concatenate(a1,b2)
=concatenate(a1," ",b2)

"Nigel Ramsden" wrote:

> Is it possible to refer to text in a cell as though it were a formula, ie 
> execute it instead of displaying it?  Eg if the result of your formula was 
> "A1 + B2", to actually add A1 and B2 and display the result?
> 
> Thanks
> 
> Nigel
0
saintolaf (56)
10/12/2005 3:25:03 PM
There's nothing built into excel that lets you do that.

But you could create a user defined function that did it.

Option Explicit
Function Eval(myStr As String) As Variant
    Eval = Application.Evaluate(myStr)
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like:   VBAProject (yourfilename.xls)  

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=eval(A24)
if A24 contained the string to evaluate.



Nigel Ramsden wrote:
> 
> Is it possible to refer to text in a cell as though it were a formula, ie
> execute it instead of displaying it?  Eg if the result of your formula was
> "A1 + B2", to actually add A1 and B2 and display the result?
> 
> Thanks
> 
> Nigel

-- 

Dave Peterson
0
petersod (12004)
10/12/2005 5:25:24 PM
One thought ...
If the result formulas that you expect are fairly limited and
predictable (eg always in the style Ref1 + Ref2), then you could
probably use the Indirect() function.
eg assuming that your result formula was in C1, then
=INDIRECT(LEFT(C1,FIND("+",C1)-1))+INDIRECT(RIGHT(C1,LEN(C1)-FIND("+"
,C1)-1)).
Of course if the result formula is more free-form, then this approach
would become so unwieldy as to be impractical.
hth
ScottO


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:434D4704.527B5054@verizonXSPAM.net...
| There's nothing built into excel that lets you do that.
|
| But you could create a user defined function that did it.
|
| Option Explicit
| Function Eval(myStr As String) As Variant
|     Eval = Application.Evaluate(myStr)
| End Function
|
| If you're new to macros, you may want to read David McRitchie's
intro at:
| http://www.mvps.org/dmcritchie/excel/getstarted.htm
|
| Short course:
|
| Open your workbook.
| Hit alt-f11 to get to the VBE (where macros/UDF's live)
| hit ctrl-R to view the project explorer
| Find your workbook.
| should look like:   VBAProject (yourfilename.xls)
|
| right click on the project name
| Insert, then Module
| You should see the code window pop up on the right hand side
|
| Paste the code in there.
|
| Now go back to excel.
|
| Then use a formula like:
|
| =eval(A24)
| if A24 contained the string to evaluate.
|
|
|
| Nigel Ramsden wrote:
| >
| > Is it possible to refer to text in a cell as though it were a
formula, ie
| > execute it instead of displaying it?  Eg if the result of your
formula was
| > "A1 + B2", to actually add A1 and B2 and display the result?
| >
| > Thanks
| >
| > Nigel
|
| --
|
| Dave Peterson


0
10/13/2005 4:32:39 AM
Reply:

Similar Artilces:

CountIF formula
I need to run a countif formula, with 2 conditions on two different columns, The first condition is that column D matches cell A1 (text), the second condition is that column K is greater than 0. I need it to count the number of rows that meet the given conditions. Please let me know if you can help -- ROSE2102 ------------------------------------------------------------------------ ROSE2102's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9202 View this thread: http://www.excelforum.com/showthread.php?threadid=507833 Hi, Rose. Try this: http://www.officear...

If specific cell is not empty, copy (or enter) formulas and format
I have a workbook that has several sheets that refer to each other with formulas throughout. The first sheet is a block of data where I enter data into columns E, G, H, P, R, S, T, U and Y. Columns A, B, C, D, F, I, J, K, L, M, N, O, V, W and X all have formulas in them (many are VLOOKUP and some are calculations). These cells all have a certain format as well (that differs from column to column), such as date format, number format (some are percentages, some are decimals with 8 decimal points, etc), conditional formatting differs for each row, etc. That being said, I n...

Aligning text
XP and 2007. I am copying and pasting text from a PDF into PPT. In PPT, when I have a bullet with two lines of text, the bottom line of text does not align with the top line. It is one space to the left. If I use the middle hanging indent mark on the ruler to move the bottom line to the right one space, the top line moves also and I must then place the cursor in front of the top line and delete a space. This is 3 extra key strokes for almost every bullet. I have tried everything I know to do. Any help will be appreciated. Thanks. There may be a way around it using code, but as ...

How do I make text italic in a publisher text box?
I've copied an item into publisher. The font automatically changed. I changed the font back to what is was originally. That worked, along with items that were once again bold. However, the entire text WAS italicized and now it's not. How do I italicize my text now? Select the text and italicize it. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "81gator" <81gator@discussions.microsoft.com> wrote in message news:35607A56-CAE8-4E6D-8FD3-FC6545B2AA9A@microsoft.com... > I've copied an item into pu...

Save text from CList into file
Hi, guys; I found a small problem with this. I thought it wasn't important but to be honest it's becoming a real pain in the ass. The question is the following: I've got a Clist with several lines created in the Dialog. At the end of the dialog, I want to save that text in a file. The code is this: void CTranslator::OnEditorEnd() { // TODO: Add your command handler code here char szFilter[] = " *.txt"; CFileDialog FileDlg( TRUE, NULL, NULL, OFN_HIDEREADONLY, szFilter); if (FileDlg.DoModal() == IDOK) { try { CFile cf(FileDlg.GetPathName() + ".txt&quo...

problem with Data|Text to Columns
I wrote a while back and was possibly not clear - I do a lot of data import and clean up - on this new computer, the subject topic, which I call data parsing, does not work correctly. I have to divide the columns with one extra space, which I never had to do before. I hate to think of uninstalling excel, but if that's what it takes . . . Any advice would be gratefully appreciated. Happy New Year -- Regards, P D Sterling New York, Texas & Texas, New York P D Sterling" <pdsterling@hotmail.com> wrote in message news:%23sslSLUTIHA.4656@TK2MSFTNGP03.phx.gbl... > call...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

Date value in cell with text
I would like a cell in Excel 2000 to have some text plus today's date, like this: "Total orders as of 3/09" I was able to pull the current date with =NOW() and right clicking the cell and choosing the date to look like 3/09 I was thinking that my formula should look like ="Total orders as of "&NOW() Any thoughts? ="Total orders as of "& TEXT(TODAY(),"m/dd") -- Kind regards, Niek Otten Microsoft MVP - Excel <tonyrulesyall@yahoo.com> wrote in message news:e37153e1-42c4-4dce-892f-ed1362033ec3@41g2000yqf.googlegroups.com... >I...

Downloadable evaluation version of Outlook 2003
does anyone know if it possible to get a downloadable version? Thanks.....Sly Sly wrote: > does anyone know if it possible to get a downloadable > version? > > Thanks.....Sly No. Not full Outlook, only Outlook Express is downloadable along with IE... >-----Original Message----- >does anyone know if it possible to get a downloadable >version? > >Thanks.....Sly >. > ...

color based on formula
I'm using vlookup to extract number from another spreadsheet such that it give e.g 4 9 +21. How do I set a specific color if a + prefix attach to a number? =VLOOKUP(B1,'Past'!A4:BE999,55,FALSE)& " " & VLOOKUP(B1,'Past'!A4:BE999,56,FALSE)& " +" & VLOOKUP(B1,'Past'!A4:BE999,57,FALSE) Do you want to explain that another way, it is not clear. -- __________________________________ HTH Bob "crapit" <abc@mail.com> wrote in message news:OxuUAkNkJHA.5732@TK2MSFTNGP05.phx.gbl... > I'm using vlookup to ...

How do you force another line in a text box?
When using text boxes. how do you force another line when character spacing reaches a certain point. A text box is a single line. Full stop. A text area will automatically wrap when the user types to the end of the visible space - this does not, however, add a line feed. To add a line feed after a preset number of characters, as I said in an earlier post, you need server side scripting which parses the data, and adds the line feed at the required intervals. Of course, this is complicated by needing to account for spaces between words - and avoiding the line feed in the middl...

How do I unwrap text to columns?
I have a spreadsheet where the addresses have been saved as wrapped text in one column. I want to spilt the addresses into columns as Address1, Address2 etc but I can't enter the wrap character (which I think is ALT+ENTER) as a delimiter in the Text to Columns wizard. I have also tried search and replace but again Excel will not recognise ALT+ENTER as a delimiter Any ideas much appreciated! Thanks Re-format the column to eliminate the wrap.............then use Data > TextToColumns with Alt010 (from the keypad) as the delimiter Vaya con Dios, Chuck, CABGx3 "ChristineR&...

Export text in ""
I want export (as a csv file) some data to create a TomTom POI file. I have Latitude, Longitude, Name I need to export the Name column in " " eg: 53.5,-0.8,"Name of location" Rather than: 53.5,-0.8,Name of location Any way to do this? -- Regards, Dean Thought I'd worked this out but it didn't work :-( Applied a custom number format to the column of \"@\" This displays " " round the text but doesn't export them to the .csv file. -- Regards, Dean "Melbridge" wrote: > I want export (as a csv file) some ...

Time Formula #2
Hi I run Excel 2K I download data from a mainframe. This data has a date & time format in it (dd/mm/yyyy hh:mm:ss) I work in a place that has a 3 shift cycle - day shift, afternoon shift, night shift. Day shift starts at 7:20am and ends at 15:29pm Afternoon shift starts at 15:30pm and ends at 23:19pm Night shift starts at 23:20pm amd ends at 7:19am I need a formula that looks at the cell with the date/time in it and displays the word DAY (for the time frame of day shift), AFTERNOON (for the timeframe of afternoon shift) and NIGHT (for the timeframe of night shift) In my previous ...

How do I embed images, text boxes, etc. in Publisher?
I'm using Publisher to send off an email newsletter but on some internet emails, they show up with attachments or are jumbled all over the page. Can anyone tell me hom I can overcome these two issues to send my newsletter? Cheers. Ricardo Not all folks will accept HTML email. Send the file as a PDF. There are free converters around. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Ricardo@turrific" <Ricardo@turrific@discussions.microsoft.com> wrote in message news:428E4744-70C7-4EAA-82F3-76E6AAED8EC5@micros...

Calculating percentages of number of occurence of a text value
I have a field that is called txtpurpose. The value of this field in any record could be 4 different values " Re-financing", "Corporate Finance", "Buy- out", or "Restructuring" For a particular month (a field txtmonth) I want to count how many records had each of these values and what percentage that number was of the total number or records for that month. So I want to end up with a reuslt like this: Re-financing 46% Corporate Finance 38% Buy-out 12% Restructuring 4% Total 100% Can someone start me off in the right direction please? Many ...

Evaluating text as formulae
Is it possible to refer to text in a cell as though it were a formula, ie execute it instead of displaying it? Eg if the result of your formula was "A1 + B2", to actually add A1 and B2 and display the result? Thanks Nigel Like concatenating text? =concatenate(a1," + ",b2) or =concatenate(a1,b2) =concatenate(a1," ",b2) "Nigel Ramsden" wrote: > Is it possible to refer to text in a cell as though it were a formula, ie > execute it instead of displaying it? Eg if the result of your formula was > "A1 + B2", to actually add A1 and...

Have you evaluated...
Has anyone evaluated Celerant Command Retail or Tomax Retail.net POS solutions? How are they compared to RMS in terms of features and prices? ...

Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...

Pinrt four line texts
#include<iostream> using namespace std class CPrint { public: ~CPrint() { cout<<"b,\n"<<"c,\n"<<"d,\n"; } }; CPrint g_print; int main() { cout<<"a,\n"; return 0; } --------------- cannot change the main function, how can I printf folowing result: a b c d ---------------------------------- If I change head file to iostream.h and delete the "using namespace std", the right result will appear. Why? my email is fisheryj # gmail.com No person know how to di it? <fisheryj@gmail...

Text import out of line?
I have the following Macro.... Can you help me to eliminate the use of any cells on the import? It comes in all chopped up but I just want it as the text file is that I am bringing from Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet Forecast\cgasdisc.txt Any HELP would be greatly appreciated. Sub Text() ' ' Text Macro ' Macro recorded 12/21/2004 by Kreft Dim destCell As Range Dim totConspWkbk As Workbook With Workbooks("DegreeDays.xls").Worksheets("text") .UsedRange.Clear Set destCell = .Cells(.Rows.Count, "A...

cells evaluation question
Hey I have a spreadsheet with 9 cells (c14:c22) They will have integers entered from 0 to 10 I need a formula to do the following: - Find how many have a value >=5 - Get the total value of the cells with a value >=5 Thanks Simon You want Countif and Sumif, as in: =countif(c14:c22,">=5") =sumif(c14:c22,">=5") Regards, Fred "Simon" <Simon@discussions.microsoft.com> wrote in message news:8D2F8EA3-D774-4A65-9157-274051900E28@microsoft.com... > Hey > > I have a spreadsheet with 9 cells (c14:c22) > They...

How do I print all the text in a cell
When I click in the cell, I can view the entire text. It will not print all of the text in the cell; it cut-off a good portion of the text. Under Format Cell, Category=text; Alignment=wrap text. How much text do you have in that cell? =len(a1) will show the number of characters in A1 If you add some alt-enters to force new lines within the cell, you should be able to see more (and print more). (Changing to a smaller font may be useful, too.) Dorna Tucker wrote: > > When I click in the cell, I can view the entire text. It will not print all > of the text in the cell; it cut...

Reducing the blank space around text in Excel 2007
In cell format\alignment the indent is set at 0which I believe is the default. So the blank space around the cell's text is the default. I would like to reduce that blank space (padding) even further but Excel will not permit me to enter a negative indent figure. Is there another way to reduce the default blank space around text in a cell? Thanks, Jeff on 7/28/2011, Jeff@nospam.invalid supposed : > In cell format\alignment the indent is set at 0which I believe is the > default. So the blank space around the cell's text is the default. > > I would like to reduce tha...

Extracting Values from Text
Thanks very much in advance for any solutions that can be offered. What formula would I use to provide the following result: A B C 1 123456-text 3" x 6 7/8" etc etc 3 6.875 2 654321-test 104 1/2" x 80 3/4" etc etc 104.5 80.75 The length is always shown in inches (indicated as such the the single quotation symbol). The length could be a whole number of fractional. The spacing and position of the text above is exact (but length of characters may vary). ...