Extract a text within a formula

sotor

New Member
Joined
Nov 8, 2018
Messages
23
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
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.
 
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!
As I was working on a solution, I did not see your last post until after I posted.
I would be interested to see that. Can you provide a link to the video that you used?
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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:
Rich (BB code):
=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:
VBA Code:
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:
Excel Formula:
=GetYear(B7)
Ah man vlookup indirect is just what I need, I will use this for my next project work. Thank you Joe!
 
Upvote 0
As I was working on a solution, I did not see your last post until after I posted.
I would be interested to see that. Can you provide a link to the video that you used?
Sure mate, this is the link
very simple VBA to create a formulatext.
 
Upvote 0
Sure mate, this is the link
very simple VBA to create a formulatext.
OK, I see. It returns the entire text of the formula, but then you would still need to do your manipulation on it to pull out just the year.
The version I posted does all that right in the code, so you don't need to mess around with it.
 
Upvote 0
OK, I see. It returns the entire text of the formula, but then you would still need to do your manipulation on it to pull out just the year.
The version I posted does all that right in the code, so you don't need to mess around with it.
True about the manipulation least I got the job done. What I will do now for the next project is to embed your code so I don't need to mess with it. Big Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
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