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:

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,170
It's only text. You would have to copy/pastespecial values, then re-enter (F2/enter) for it to calculate.
 

gth802s

New Member
Joined
Sep 29, 2014
Messages
4
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?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,788

ADVERTISEMENT

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.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows
Add zero to it

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

gth802s

New Member
Joined
Sep 29, 2014
Messages
4
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 :(
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,788
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,173
Members
409,854
Latest member
rickcoba
Top