Extract a text within a formula

[sotor]

Hello,

I am trying to figure out how to extract a particular text or number within a formulated cell. For example

In cell A7 the formula is =VLOOKUP(A7,'EPO 2019'!A:E,5,FALSE) what I want to achieve in cell B7 is to extract the year from the formula in A7 so B7 returns the value 2019.

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

Excel has a function named "FORMULATEXT", which allows you to pull the text of the formula, but it is only available on newer versions of Excel (FORMULATEXT function).
So it would be helpful for us to know what version of Excel you are using.

Also, can you post a few other possibilities of what the sheet names will look like?
Will they ALWAYS end in the 4 digit year?

 

[Joe4]

If you do have a newer version of Excel and your sheets always end in a 4 digit year, this formula would do what you need:

=RIGHT(TRIM(MID(SUBSTITUTE(FORMULATEXT(A7),"'",REPT(" ",100)),100,100)),4)

 

[sotor]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

Excel has a function named "FORMULATEXT", which allows you to pull the text of the formula, but it is only available on newer versions of Excel (FORMULATEXT function).
So it would be helpful for us to know what version of Excel you are using.

Also, can you post a few other possibilities of what the sheet names will look like?
Will they ALWAYS end in the 4 digit year?

Thanks Joe, I've updated the account details. I have different version Excel (personal Excel 2019) Work Excel is main one I am using and it's the retired version 2010.

 

[Joe4]

Thanks Joe, I've updated the account details. I have different version Excel (personal Excel 2019) Work Excel is main one I am using and it's the retired version 2010.

OK, so it looks like the this formula was not available on Excel 2010 (it would work fine on 2019). So I think you may be out-of-luck there, if you were hoping to use that formula on Excel 2010.
The only other option I can think of would require VBA. Are you open to using VBA?

There may be other ways of approaching this too. Obviously, someone had to consciously enter that in the formula, so someone knows what sheet is being referenced.
So, that begs the question, what is the purpose/goal of trying to pull the "2019" out of the formula?
What are you going to be using that for?

I have some other ideas that may work, if I understand what the bigger picture is.

 

[sotor]

If you do have a newer version of Excel and your sheets always end in a 4 digit year, this formula would do what you need:

=RIGHT(TRIM(MID(SUBSTITUTE(FORMULATEXT(A7),"'",REPT(" ",100)),100,100)),4)

Didn't quite work it returns #NAME?

 

[Joe4]

Didn't quite work it returns #NAME?

See my previous post.

 

[sotor]

OK, so it looks like the this formula was not available on Excel 2010 (it would work fine on 2019). So I think you may be out-of-luck there, if you were hoping to use that formula on Excel 2010.
The only other option I can think of would require VBA. Are you open to using VBA?

There may be other ways of approaching this too. Obviously, someone had to consciously enter that in the formula, so someone knows what sheet is being referenced.
So, that begs the question, what is the purpose/goal of trying to pull the "2019" out of the formula?
What are you going to be using that for?

I have some other ideas that may work, if I understand what the bigger picture is.

I don't mind using VBA but as a last option, prefer quick formula if possible. In answer to your question on the purpose of - the same column contains a mix of vlookup formula that returns the values of three worksheets that contain the year 2017, 2018 and 2019. I want to use (extract the year value) so that I can categorise/filter the year by monetary values.

 

[sotor]

Actually solved using Bill's video on YT Mr Excel, using simple VB. Very helpful. Thank you Joe for your efforts, if you managed to the find the formula, worth sharing, thank you!

 

[Joe4]

One thought I had was instead of updating all the formulas manually to look at the appropriate sheet, put the sheet name in some cell, and then use the INDIRECT function in your VLOOKUP formula to look at that cell reference instead of hard-coding it right into the formula.

For example, if you put "EPO 2019" in cell A6, then your lookup formula could look like this:

=VLOOKUP(A7,INDIRECT("'" & A6 &"'!A:E"),5,FALSE)

The advantage there is that instead of trying to pull the year out of the formula, you can just pull it out of A6 with a simple formula.

If that is not a viable solution, then I would probably create my own function in VBA to extract the year.
That User Defined Function, which needs to be inserted into a new VBA module in your workbook, may look like this:

Function GetYear(cell As Range) As Variant

    Dim frml As String
    Dim st As Long
    Dim ed As Long

'   Only run on a single-cell range
    If cell.CountLarge > 1 Then
        GetYear = "Too many cells"
    Else
'       Get formula
        frml = cell.Formula
'       Get starting and ending spots of formula
        st = InStr(frml, "'") + 1
        ed = InStrRev(frml, "'") - 1
'       Check to see if value found
        If ed <= st Then
            GetYear = "No sheet name found"
        Else
            GetYear = Right(Mid(frml, st, ed - st + 1), 4)
        End If
    End If

End Function

Then you would just use it like any other function, i.e.
if your formula was in cell B7, then you would use this formula to pull the year out:

=GetYear(B7)