Convert Text to a Formula in Excel | The CRAZY method for tough Excel problems.

unfortunately your Excel skills have backed you into a corner there's nowhere else to turn there's only one thing for it you need to turn text into a formula what that sounds crazy I know I know but stick with me this isn't your typical everyday Excel solution this is where you go where you've got nothing else to try so how can we turn text into a formula let's find out here we are in Excel let's start with a simple example we have cells with the values of 1 + 1 and then we've used the concat formula to get that together into a single text value seriously dude just Ty equals 1 + 1 into a cell as a standard formula and be done with it that's not the point we're looking at Concepts here and then seeing how we can then apply it to different scenarios now Excel has a language built in which is called Excel for macros this is an older language which isn't used anymore but it still works and within that language we have have the evaluate function evaluate takes text and based on that text it calculates a result unfortunately in Excel if I type equals evaluate Open Bracket I'll select the text and then close that bracket what happens is that we get an error message so evaluate doesn't work correctly by itself but what if we added our evaluate function inside a Lambda function inside a named range that sounds crazy right let's go and take a look so I'll go to formulas and then Define name and I'm going to call this FX text to formula then in then refers to equals Lambda Open Bracket the first argument will be a string and then we're going to pass that into our evaluate and then we're going to use the string now because we need to make sure that our formula calculates correctly we need to make this a volatile function just like indirect is a function that takes text and converts to a range that's a volatile function we need to do the same with our function that turns text and turns it into a formula to do that we're going to add the T function so that extracts any text and we're going to use inside that the Now function so that's going to get the current datee and time which doesn't contain conin any text therefore this T and Now function combination is going to return an empty text string but it does change it into a volatile function so we'll close our evaluate and then we'll close our Lambda and press return okay let's try this out equals FX can see our function there FX text the formula we'll select cell C5 close that bracket boom look at that it calculates the correct result that is fantastic we've now created our own function to turn text into a formula hi there I'm Mark from Excel Off the Grid that's the place where we teach people how to reclaim huge amounts of time by automating Excel so if you want to save a lot of time why not head over to excel ford.com and check out our training courses now you might be thinking when on Earth are you ever going to use this so let's go and look at a use case that you might actually need so here in Excel I have a table with item and region and also value down here I have the filter criteria that I want to apply to this table and then I've created a filter function using this criteria so that means that now I've got this filter function as text I can use our FX text to formula so far this might not sound that useful but in the filter function we can't have a variable number of conditions unless of course we use our FX text to formula so here I can add another condition value where it's less than or equal to 25 and now our filter function updates we can also use it with text where region is equal to South look at that we now have everything where the value is bigger than 15 the value is less than or equal to 25 and the region is South so into our filter function we've been able to add multiple conditions using our FX text to formula function as the method that evaluates our formula we're not finished yet if the concept of using a Lambda and an EXL for macro just seems too crazy or possibly you don't have the right version of excel I've got another solution for you which uses a VBA user defined function so let's head over into the Visual Basic editor I'm going to select my workbook and then click insert module this creates a new code module and I'm going to paste the code in so this creates our function and our function is called FX text to formula UDF and it is text so it accepts text as an argument we then make that function volatile and then we declare what the result of that function should be and that uses the application. caller. parent. evaluate so that's the same function as we used inside our Lambda and that's it we can now head back to Excel and then we can use our FX you see there text to formula UDF open bracket let's select our original cell close that bracket press return and that also calculates that value for us so there we have it we have a Lambda method and a VBA method for both of these Solutions we do need to save them as macro enabled workbooks so they need to be saved as xlsm files at the start you probably thought the idea of converting text to a formula seemed crazy but actually there might be a really good use case if you enjoyed this video why not click here to subscribe and if you want some more Excel fun that's the place you need to go to thanks for watching I'll catch you next time

Channel: Excel Off The Grid

Views: 6,872

Keywords:

Id: iS7FTSZcvNo

Length: 6min 24sec (384 seconds)

Published: Thu May 23 2024