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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
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
24,210

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,717
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
24,210
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,447
Members
430,549
Latest member
jayjay2022

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
Top