Avoid duplicate code in Excel IF formula code

1
votes

I want to avoid duplicate code within excel formulas. Is there a method to repeat a certain code segment?

=IF(A1=1,(A1-B2-C3),(A1-B2-C3)+1)

This would be especially useful when it comes to more complex or longer sections. But: everything must be in ONE formula in ONE cell. Thanks! :-)

EDIT: This is my current code. 

=IF(ISNUMBER(SEARCH(".amp",A2)),IFERROR(MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1,SEARCH(".html",A2)-FIND("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-5),""),IFERROR(MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1,SEARCH(".html",A2)-FIND("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1),""))

It strips the long ID number out of any URL of a specific CMS. So 

FIND("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))

is probably the part which occurs more than once and should be replaced for a code which does not be that duplicate-prone.

EXAMPLE: www.domain.com/path1/path2/this-is-an-article-123-dd-123456789.html --> 1234567890 EXAMPLE: www.domain.com/path1/path2/this-is-an-article-123-dd-1234567890.amp.html -> 1234567890 EXAMPLE: www.domain.com/path1/this-is-an-article-1234567890.html -> 1234567890

3
regexexcelgoogle-sheetsexcel-formulagoogle-sheets-formula
=IF(A1=1,0,1)+(A1-B2-C3) - BigBen
Thanks very much! I wanted to express that this was an example - in reality, it may contain of something much more complex. E.g. IF(A1=1,COMPLEXFORMULA1_WITH_NUMBER_RESULT,COMPLEXFORMULA1_WITH_NUMBER_RESULT-1+A3/F7) .. or is your method also working in this case? - David
It seems like you can still apply the same principle to that complex formula - keep the 1+A3/F7 inside and move the COMPLEXFORMULA1_WITH_NUMBER_RESULT outside. - BigBen
Put this in question and edit the question instead. Comments are harder to read. - shrivallabha.redij
Please include: Input samples and expected output sample. There might just be a smarter way to do what you try to do. And tell us which of the two apps you are using: Excel or Google Sheets. They operate differently. - JvdV

3 Answers

1
votes

Place the complex common sub-expression in its own cell and refer to that cell.

EDIT#1:

As an alternative, you can use a Named Formula for the sub-expression:

Named Formula

1
votes

In google sheets, you could use REGEXEXTRACT to get what you want:

enter image description here

Formula in B1:

=REGEXEXTRACT(A1,"\d{8,}")
0
votes

So here is another way of finding the code in Excel:

Solution

Here is the formula in Cell B1 which needs to be confirmed by pressing Ctrl+Shift+Enter, then drag it down to apply across board:

{=FILTERXML("<data><a>"&SUBSTITUTE(MID(A1,LARGE(IF(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1)="-",ROW($A$1:INDEX($A:$A,LEN(A1)))),1)+1,LEN(A1)),".","</a><a>")&"</a></data>","/data/a[1]")}

For the logic behind this formula you may give a read to this article: Extract Words with FILTERXML.

Cheers :)

Ps. it seems that GoogleSheet has out performed Excel in some area already.