Convert Literal Text to Formula

excel text excel-formula excel-indirect
Miaka3 picture Miaka3 · Apr 30, 2016 · Viewed 14k times · Source

Using MS Excel 2010: I used the CONCATENATE formula to create a text string that looks like a formula and need a formula that will convert the text string to a formula; without the use of MS Excel Paste Special function or VBA/Macro.

Example: In Cell B2:G2 contains text, in Cell B4 I have CONCATENATE text formula that returns a text string 

=TRIM(CONCATENATE(B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2))

I want Excel to interpret this string as a formula and return/show the value in Cell B6 as: "Always be yourself … do not go out and look for a successful personality and duplicate it.” - Bruce Lee

enter image description here

I have attempted using the INDIRECT formula and without success, not certain if it's my formula or if it's possible. SAMPLE: Cell B6:

=INDIRECT(CONCATENATE("B4"))

Answer

Forward Ed picture Forward Ed · Apr 30, 2016

1) CONCATENATE and & are the same thing. In other words the formula you wrote as a string could be translated as:

=TRIM(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(B2)," "),C2)," "),D2)," "),E2)," "),F2)," "),G2)," "),H2))

If you are going to write out CONCATENATE for you function, then separate all he strings you want joined together as with a coma as has been pointed out by @MarcoVos in the comments to your question:

=CONCATENATE(B2," ",C2," ",D2," ",E2," ",F2," ",G2," ",H2)

Or if you want to use the more common form of & your formula would look like what you originally posted without the CONCATENATE out from like this:

=B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2

Those last two formulas will produce the same results.

2) The INDIRECT function will convert text to a cell references or address. It will not convert a formula as text and spit out the results of the formula. Macros Vos in his answer is correct in that FORMULATEXT() will display the formula in the referenced cell as text or a string. If you need to follow a sequence where you provide the string and then must convert the string into a formula, then I suggest you use the EVALUATE Function. You cannot use EVALUATE from a regular excel formula though. You can call it from a named range though. Create a named range. Lets for example call it EvalText. In the formula portion for creating the named range, enter:

=EVALUATE($B$4)

It will automatically add the sheet name. Now in any cell on your sheet you can enter:

=EvalText

and it will return whatever the string in B4 works out to be as a formula. It will spit out an error if its not a proper excel formula.

Related questions

Insert characters into string in different locations

I have a string which looks like this 20141007023617. And I would like to make it look like this 2014-10-07T02:36:17Z. The T and Z are very important for me. So I would like to insert them as well. …

Read More
How do I Replace a String in a Line of a Text File Using FileSystemObject in VBA?

I am trying to use FileSystemObject methods to find a specific line in a text file, and within that line replace a specific string. I am relatively new to this, as my current code has excel open the text file …

Read More
Python Throwing "'utf8' codec can't decode byte 0xd0 in position 0" Error

I am trying to load a currently existing worksheet and import the text file (comma separated values) screenshot shown below, Excel Sheet: Text File: I am using the code shown below: # importing necessary modules for performing the required operation import …

Read More