Can I create a formula using concatenate?

=CONCATENATE("=",3,"+",2)

gives me:
=3+2

instead of what I want:
5

How can I create a formula using concatenate?
This =3+2 does not seem to be text nor value, what is it?

If you download the Morefunc add-in you can use the EVAL function to do this, e.g.

=EVAL(CONCATENATE("=",3,"+",2))

get Morefunc from here

http://xcell05.free.fr/english/

I've been trying to do something similar. You can copy and then pastespecial-values, but it will still just give you the =3+2 string. However, when you then click in the formula bar and hit enter, the formula takes effect.

Problem for me is; I have to do this thousands of times. Would it be possible to write a script to do this for many cells at once? What would that look like? Any thoughts or examples would really help me out.

I am not sure if you all have finally solved this, but I was trying to figure out the same thing and nothing online would help. Since I have found the answer, I will share it:

I am trying to access this page:='C:\Users\bb288\Desktop\Day Sheets\2013\[Feb 2013.xlsx]1'!$B$26

I needed to make it a dynamic formula because it is on a template that we copy each month, so when I change the name of the month and I change the year, it will refer to a different folder and/or a different month within the folder.

My final equation is: =INDIRECT(CONCATENATE("'C:\Users\bb288\Desktop\Day Sheets\",INDIRECT("'Net'!B2")-1,"\[",LEFT(Net!$B$3,3)," ",INDIRECT("'Net'!B2")-1,".xlsx]",DAY(B3),"'!B26"))

I use different functions and worksheets/cells to pull all the reference information from where it already sits rather than making a new reference page and to create the proper terms for the nomenclature of our books/sheets.

The formula broken down returns: =INDIRECT(CONCATENATE("'C\Users\bb288\Desktop\Day Sheets\",2012,"\[",Feb," ", 2012,".xlsx]",1,"!B26"

or: =INDIRECT('C:\Users\bb288\Desktop\Day Sheets\2012\[Feb 2012.xlsx]1'!B26) which returns the correct number for the corresponding cell.


If you are trying to do this yourself and keep getting errors, I definitely suggest using the "Evaluate Formula" tool to find out where you are going wrong. Also, make sure that you are using the proper type of document. I just spent 2 hours combing through the formula to figure out what was wrong and the issue turned out to be .xls instead of .xlsx.

Good Luck!

Oh, also, use the offline coding for the workbook rather than the coding for an open workbook since it will work with the book being referenced open or closed rather than only open.

Thanks!! I had last done this about 7 years ago and completely forgotten the function I had used, INDIRECT.

Originally Posted by tbhesswebber

I am not sure if you all have finally solved this, but I was trying to figure out the same thing and nothing online would help. Since I have found the answer, I will share it:

I am trying to access this page:='C:\Users\bb288\Desktop\Day Sheets\2013\[Feb 2013.xlsx]1'!$B$26

I needed to make it a dynamic formula because it is on a template that we copy each month, so when I change the name of the month and I change the year, it will refer to a different folder and/or a different month within the folder.

My final equation is: =INDIRECT(CONCATENATE("'C:\Users\bb288\Desktop\Day Sheets\",INDIRECT("'Net'!B2")-1,"\[",LEFT(Net!$B$3,3)," ",INDIRECT("'Net'!B2")-1,".xlsx]",DAY(B3),"'!B26"))

I use different functions and worksheets/cells to pull all the reference information from where it already sits rather than making a new reference page and to create the proper terms for the nomenclature of our books/sheets.

The formula broken down returns: =INDIRECT(CONCATENATE("'C\Users\bb288\Desktop\Day Sheets\",2012,"\[",Feb," ", 2012,".xlsx]",1,"!B26"

or: =INDIRECT('C:\Users\bb288\Desktop\Day Sheets\2012\[Feb 2012.xlsx]1'!B26) which returns the correct number for the corresponding cell.


If you are trying to do this yourself and keep getting errors, I definitely suggest using the "Evaluate Formula" tool to find out where you are going wrong. Also, make sure that you are using the proper type of document. I just spent 2 hours combing through the formula to figure out what was wrong and the issue turned out to be .xls instead of .xlsx.

Good Luck!

An easy way to turn your text-only calculations into results is to copy the cells and paste them in Notepad, and then just copy them from Notepad and past them back into your worksheet. It works like a charm for me every time.

Thanks for the welcome FDibbins! Yes, it may not be practical for all situations, but it sure was for mine. I figured there are others out there like me who just need a quick fix. (I tried using indirect and for whatever reason, it wasn't working for me. I can't locate a download link for morefunc that works either.)

Another way is to set up a UDF like this:

Please Login or Register  to view this content.

and then you can use:

=eval("A1 + A2")

for example, or:

=eval(B1)

where B1 contains the string A1 + A2.

Hope this helps.

Pete

I found when I was getting used to building formulas once i got my head around the different operators and what they represented it really helped me on my way. I found the following really useful information, which may also help you if you are getting stuck. https://goo.gl/dQxjgf

Originally Posted by psiersma

I've been trying to do something similar. You can copy and then pastespecial-values, but it will still just give you the =3+2 string. However, when you then click in the formula bar and hit enter, the formula takes effect.

Problem for me is; I have to do this thousands of times. Would it be possible to write a script to do this for many cells at once? What would that look like? Any thoughts or examples would really help me out.

I fixed this problem by using the "Replace All" menu feature: Find = (equal sign), Replace With = (equal sign). :-)

I am sucessfully using it in a three steps approach with VLOOKUP formula :

=CONCATENATE("=";"VLOOKUP(H4;'";'Visual Parley Result Vend'!$E$4;"17'!$B$9:$N$27;13;FALSE)")

It then yields =VLOOKUP(H4;'Jan17'!$B$9:$N$27;13;FALSE)

Next, past especial values

=VLOOKUP(H4;'Jan17'!$B$9:$N$27;13;FALSE)

Finally, Replace All = by = and the formula takes effect. It saves long-long time:!

Done!. Good luck.

Originally Posted by psiersma

I've been trying to do something similar. You can copy and then pastespecial-values, but it will still just give you the =3+2 string. However, when you then click in the formula bar and hit enter, the formula takes effect.

Problem for me is; I have to do this thousands of times. Would it be possible to write a script to do this for many cells at once? What would that look like? Any thoughts or examples would really help me out.

This is a super old post, so you probably already figured out another solution, but I was just trying to this same thing and found that you can copy the concatenated formulas, paste values, and run a "replace all" on the cells with the formulae, simply replacing all "=" with "=" and it will force the cells to run the formulae.