# find character location in cell formula

#### craigexcel

##### Active Member
While I know how to find the location of a specific character in a cell's returned value, I can't quite seem to figure out, or find out, how to determine the location of a specific character in a cell's formula. Is there a way to do this?

For example, if a cell formula is: =IF(A10=1,SUM(B10+C10),SUM(D10-E10))
how would I find the the 'start number' (taking the term used in the MID function) of the first "10" in the formula itself?

Also, is there a way to determine how many references there are to "10" in the formula, in this case --- 5?

### 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.
While I know how to find the location of a specific character in a cell's returned value, I can't quite seem to figure out, or find out, how to determine the location of a specific character in a cell's formula. Is there a way to do this?

For example, if a cell formula is: =IF(A10=1,SUM(B10+C10),SUM(D10-E10))
how would I find the the 'start number' (taking the term used in the MID function) of the first "10" in the formula itself?

Also, is there a way to determine how many references there are to "10" in the formula, in this case --- 5?

For Excel <2013 Use this UDF:
Code:
``````Function IsFormula(c)
IsFormula = c.HasFormula
End Function``````
Excel 2013 has native FORMULATEXT function

While I know how to find the location of a specific character in a cell's returned value, I can't quite seem to figure out, or find out, how to determine the location of a specific character in a cell's formula. Is there a way to do this?

For example, if a cell formula is: =IF(A10=1,SUM(B10+C10),SUM(D10-E10))
how would I find the the 'start number' (taking the term used in the MID function) of the first "10" in the formula itself?

Also, is there a way to determine how many references there are to "10" in the formula, in this case --- 5?

In what circumstances do you want to use this? If within VBA, then it is fairly straightforward as you can access the Formula property of the Range object to determine the formula contents. You can write this into a UDF if you want to use it from within Excel.

If you want to extract the formula string exclusively using formulas and you have an earlier version than Excel 2013 then it is more difficult (it would mean using old XLM 4 Macro Functions).

I forgot to mention I'm using Excel 2007. I was originally thinking a formula would be good, but if that's not possible, or is much more difficult to do, VB would work too. I'm not familiar with UDFs at this point, but am always interested to learn.

I forgot to mention I'm using Excel 2007. I was originally thinking a formula would be good, but if that's not possible, or is much more difficult to do, VB would work too. I'm not familiar with UDFs at this point, but am always interested to learn.

UDF(User Define Function) - is being enter in the same way as standart VB code.
Go to Developer tab--Visual Basic.
In the new window click Insert-Modul and add the code provided.
Then when in Excel sheet this functionwill be available when you start to type formulas in cell.

While I know how to find the location of a specific character in a cell's returned value, I can't quite seem to figure out, or find out, how to determine the location of a specific character in a cell's formula. Is there a way to do this?

For example, if a cell formula is: =IF(A10=1,SUM(B10+C10),SUM(D10-E10))
how would I find the the 'start number' (taking the term used in the MID function) of the first "10" in the formula itself?

Also, is there a way to determine how many references there are to "10" in the formula, in this case --- 5?
I am curious... what are you doing that you need a formula to have access to another cell's formula? Given the way Excel distributes formulas that are copied down, it would seem that you could calculate row or column designations given a starting cell's address without resorting to looking at the actual formula.

Replies
1
Views
70
Replies
9
Views
143
Replies
3
Views
138
Replies
6
Views
173
Replies
5
Views
169

### Forum statistics

1,203,198
Messages
6,054,073
Members
444,701
Latest member
PTDykman ### 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.

### Which adblocker are you using?    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

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