+ Reply to Thread
Results 1 to 16 of 16

Can I create a formula using concatenate?

  1. #1
    Registered User
    Join Date
    03-03-2007
    Posts
    37

    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?

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by 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?
    Hi,

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

    You cannot produce formulae this way.

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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/

  4. #4
    Registered User
    Join Date
    10-14-2009
    Location
    Utrecht, Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Can I create a formula using concatenate?

    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.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Can I create a formula using concatenate?

    psiersmaYour 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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  6. #6
    Registered User
    Join Date
    02-03-2013
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Can I create a formula using concatenate?

    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!

  7. #7
    Registered User
    Join Date
    02-03-2013
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Can I create a formula using concatenate?

    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.

  8. #8
    Registered User
    Join Date
    04-10-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Can I create a formula using concatenate?

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

    Quote Originally Posted by tbhesswebber View Post
    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!

  9. #9
    Registered User
    Join Date
    07-11-2013
    Location
    Hawaii, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Can I create a formula using concatenate?

    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.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can I create a formula using concatenate?

    Plumaei, welcome to the forum

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  11. #11
    Registered User
    Join Date
    07-11-2013
    Location
    Hawaii, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Can I create a formula using concatenate?

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

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can I create a formula using concatenate?

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

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Can I create a formula using concatenate?

    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

  14. #14
    Registered User
    Join Date
    03-15-2016
    Location
    London, England
    MS-Off Ver
    MS Office
    Posts
    1

    Re: Can I create a formula using concatenate?

    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

  15. #15
    Registered User
    Join Date
    02-12-2017
    Location
    Caracas, Venezuela
    MS-Off Ver
    2010
    Posts
    1

    Re: Can I create a formula using concatenate?

    Quote Originally Posted by psiersma View Post
    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.

  16. #16
    Registered User
    Join Date
    05-05-2022
    Location
    MI, USA
    MS-Off Ver
    365
    Posts
    1

    Re: Can I create a formula using concatenate?

    Quote Originally Posted by psiersma View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1