# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Can I create a formula using concatenate?

## rohed

=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?

----------


## Bryan Hessey

> =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?



Hi,

Concatenate produces Text strings, and what you have is a concatenation of 4 (text) characters.

You cannot produce formulae this way.

hth
---

----------


## daddylonglegs

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/

----------


## 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.

----------


## NBVC

psiersma_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------


## 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!

----------


## tbhesswebber

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.

----------


## lawrence001

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





> 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.
> ...

----------


## Plumaei

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.

----------


## FDibbins

Plumaei, welcome to the forum  :Smilie: 

Your method is perfect for once-off situations or the occaisional conversions.  But is becomes impractical in situations where you have multiple references, or where the references constantly change due to data updates.

In these situations, INDIRECT() is the way to go, and using morefunc will enable you to reference closed workbooks  :Smilie:

----------


## Plumaei

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.)

----------


## FDibbins

"whatever works" is often the best way to go, and using INDIRECT() can be tricky until you get the hang of it

----------


## Pete_UK

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

----------


## June Martin

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

----------


## tambirita

> 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.

----------


## ctruelove

> 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.

----------

