Converting text to formula

gth802s

New Member
Joined
Sep 29, 2014
Messages
4
Hi,

New member here; I did a search on the subject but it didn't seem to answer my question so here it goes.

I am using the "concatenate" function to combine text that is stored in three separate cells:

=CONCATENATE(Text!A1,Data!BC2,Text!A2)

where:

Text!A1 = VLOOKUP($B6,'C:\Users\gth802s\Dropbox\Habersham EMC Data Analysis\2009 Retrofits\[
Data!BC2 = Clark,Randall L
Text!A2 = -2009_usagereport.xls]Sheet1'!$C$22:$P$4000,4,FALSE)

with the result being:

VLOOKUP($B6,'C:\Users\gth802s\Dropbox\Habersham EMC Data Analysis\2009 Retrofits\[Clark,Randall L-2009_usagereport.xls]Sheet1'!$C$22:$P$4000,4,FALSE)

What I would like to do is to have the command evaluated directly in the cell so that the Vlookup function is executed and the cell displays the result directly. I tried putting an equal sign in front of the expression as follows:

="="&CONCATENATE(Text!A1,Data!BC2,Text!A2)

but this doesn't seem to help (I am guessing this is because Excel still sees it as text).

Any suggestions on how to get this to execute in the cell?

Thanks in advance,

-Fikret


<tbody>
</tbody>
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
It's only text. You would have to copy/pastespecial values, then re-enter (F2/enter) for it to calculate.
 
Upvote 0
Thanks. That is sort of what I have been doing, but I have do this hundreds of times so I wanted to see of there was a way to automate the process.

Any other thoughts?
 
Upvote 0
Select the cell where you want the result.
Define a name

Name: EvaluateConcat
RefersTo: =EVALUATE(CONCATENATE(Text!A1,Data!BC2,Text!A2))

Put =EvaluateConcat in the cell

Note that because of the relative addressing, which cell is selected when defining the name is important.

EVALUATE is a legacy function that works in Names and VBA but not formulas on a worksheet.
 
Upvote 0
Select the cell where you want the result.
Define a name

Name: EvaluateConcat
RefersTo: =EVALUATE(CONCATENATE(Text!A1,Data!BC2,Text!A2))

Put =EvaluateConcat in the cell

Note that because of the relative addressing, which cell is selected when defining the name is important.

EVALUATE is a legacy function that works in Names and VBA but not formulas on a worksheet.

Thanks for the tip. I tried doing this but when I did, the cell returned "#REF!".

Add zero to it

="="&CONCATENATE(Text!A1,Data!BC2,Text!A2)+0

Thanks for the suggestion, but this didn't work either :(
 
Upvote 0
Practice with the EVALUATE approach, I'm not sure that your concatenated formula is in the proper syntax. are you sure it is an .xls file and not .xlsx?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top