find character location in cell formula

[craigexcel]

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?

 

[Robert Mika]

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:

Function IsFormula(c)
IsFormula = c.HasFormula
End Function

Excel 2013 has native FORMULATEXT function

 

[Firefly2012]

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).

 

[craigexcel]

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.

 

[Robert Mika]

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.

 

[Rick Rothstein]

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.