Extract value from Excel formula

April 22, 2009 at 09:34:03
Specs: Windows XP
As shown below, if I had entered different number under Col. A, and formulas under Col. B. As you can see the formulas are not in sequence with row number. It could be linked to any rows for the calculation. As a result, I can't use "=ROW()". So how can I extract the value (part of the row number within the formula) and have it placed under Col. C??

BTW, The extra dots were added for spacing only.

Column A Column B Column C

5...... =A3*3......... 3
8...... =A4*3......... 4
6...... =A1*3......... 1
9...... =A2*3......... 2
4...... =A5*3......... 5

See More: Extract value from Excel formula

Report •

April 22, 2009 at 10:57:13
I would like to ask why you are trying to do this.

The solution below will work for your specific example, but it it is not universal. Perhaps if you provided some more details, we could offer some more help.

The main reason this works is because all of your formulae multiply the value in Column A by 3. That makes it possible to autofill this formula down and "extract" the correct Row number. I know it's not really extracting anything, but it does produce the same results as your example.


Report •

April 22, 2009 at 17:44:24
Sometimes it just takes some perseverance with Google and a little tweaking of what you find out there.

So, to give credit where credit is due, I started with what I found at:


I then modified that UDF to get the UDF shown below.

To use this UDF, copy the code into a standard VBA module. Then enter =GetRow(cell_ref) in any cell in the worksheet and it should extract the row number from the formula in your example.

Note: This UDF is extracting the row number based on the location of the multiplication operator (*). To make it work with other formulae, it will need to be modified as needed.

Function GetRow(R As Range) As Variant
  For Digit = Len(R.Formula) To 1 Step -1
   On Error Resume Next
    myRow = myRow & Mid(R.Formula, InStr(R.Formula, "*") - Digit, 1) * 1
 GetRow = myRow
End Function

Report •

April 23, 2009 at 08:31:47
I tried it and didn't quite get the result I was looking for. Maybe I'll show you what I'm trying to and what the formula looks like, then you can change the command lines accordingly.

I have a master sheet (Sheet1) that has over 700 rows of data. Dates are entered under Col.E when events happened. From this sheet, I have created over 50 different sheets, named Sheet2, Sheet3.....Each one of these sheets will have various rows out of the 700 from Sheet1. Part of the purpose of this worksheets is to have the date show up on all corresponding cells of the subsequent sheets when a date is entered on Sheets1. Right now I have column H on all subsequent sheets showing (manually entered) the original row number that the row is on Sheet1. The problem I have is whenever I make changes (add or delete) rows of Sheet1, I will have to go to every other sheet that was affected by the change of row number and manually correct them. So if I could, instead of manually have the row number entered and corrected everytime when it's needed, insert a formula or something to extract the row number from the formula in Col.B that will make any future modifications a lot easier.

Below is a formula that is in Col. B on any worksheets (from Sheets 2 on) that refers to, in this case, row 18 on Sheet1


I only need something that will in this case, extract the number 18 and place on the same line (whatever it might be) in Col. H

I hope I have clearified this a bit better and hopefully you can alter the UDF to give the desire result. Thank you.

Report •

Related Solutions

April 23, 2009 at 09:38:17
I'm still slightly confused.

Your formula refers to Row 18 twice, both in the logical_test portion and in the value_if_true portion. Is this the same for all of the formulae that you are trying to extract the row number from? If not, which of the 2 row references do you want?

This version of the UDF should handle your current example by extracting the row number from the logical_test portion.

My concern is that it is written based on certain characters in the formula and may not work generically. Basically I'm searching for numbers between the first $ and the second =. i.e. In the bold section below ...


That said, I'm not going to claim to completely understand what you are actually doing with this number but for some reason the INDIRECT function comes to mind.

With a row number in a cell, such as 18 in H34, these 2 formulae are equivalent:


=IF(INDIRECT("Sheet1!$E" & H34)="","",INDIRECT("Sheet1!$E" & H34))

Is this something you can use in your project? I'm thinking that you could change a value in a cell and the change would propagate through all INDIRECT references to that cell.

Anyway, here's the new version of the UDF:

Function GetRow(R As Range) As Variant
  For Digit = InStr(2, R.Formula, "$", 1) To InStr(2, R.Formula, "=", 1)
   On Error Resume Next
    myRow = myRow & Mid(R.Formula, Digit, 1) * 1
 GetRow = myRow
End Function

Report •

April 23, 2009 at 10:10:51
Well I'm not very good with these stuff. My formula could probably be written better in another way. All that formula will do is to check that particular cell in Sheet1. If it has nothing, then it prints nothing (left blank) on all subsequent sheets at the relative cells. OR print the date if it's entered on Sheet1.

Anyway, the new version of the UDF works like a charm. I really appreciate all the help you've given me. I can't image how much time I'll be saving in the future whenever I have to make any modifications. Thanks again and have a nice day.

Report •

April 27, 2009 at 15:50:48
There seems to be a little problem with this. After I inserted the UDF to cells where it's needed, the value (row number) would appear as expected. But after I closed the file and when I opened it again, the value is no longer there. In stead, I would get a "#VALUE!" error. How can I get around this so it will retain the value? TIA.

Report •

April 27, 2009 at 16:51:31
I can't say what the problem is because when I save, close and open the file the function still returns the row number as expected.

Report •

April 28, 2009 at 06:57:19
What I mean is after I added or deleted some rows off the master sheet (Sheet1). When I checked any of the other sheets, I would get that error (#VALUE!). One way to correct that is to go to the first row of each sheet that has the inserted UDF, double-click it, and then the value will re-appear. Then I would fill-down the whole column that has the UDF to get the number back. The problem is I would have to do this manually to each sheet just to update the changes. I'm just wondering if there's any way that it will update automatically. TIA.

Report •

April 28, 2009 at 16:01:42
I don't know. I'll have to play with that.

If you click on a cell that shows #VALUE I believe that you are going to see a #REF error where the cell reference used to be. That's because the cell the UDF was referring to has moved.

I'm not sure how to tell the UDF what the new cell reference should be.

Report •

April 28, 2009 at 16:53:38
I've sent you an email address via PM.

Is there any chance you could send me a copy of the workbook - with any sensitive data deleted - so I can see what you are trying to do?

I'm wondering if there is another way to accomplish your goal instead of chasing problems with the UDF.

Report •

April 29, 2009 at 09:00:52
Just as I was about the send you a copy of the file, I found out how to fix my problem. I have originally inserted a blank sheet named "Completed" in between all the sheets to separate the finished and unfinished sheets. Like I said, for whatever reason, the cells wouldn't update automatically whenever I add or delete some rows off the master sheet. But as soon as I delete this blank sheet, every cell in every sheet that has the UDF will have the proper row number in place of the "#ERROR!". That's excellent, it means I will not have to go to each sheet, double-click and fill-down to fix the problem anymore. Anyway thanks again and I appreciate all the help and advises you have given me.

Report •

Ask Question