## Break Formula

3StarLounger
Posts: 211
Joined: 04 Oct 2017, 15:47

### Break Formula

Hi all

how i can break below formula in pieces for make workable

i believe due to single long line its not working

Code: Select all

``x = Evaluate("=IF(""" & Cells(2, "b") & """="""","""",LOOKUP(9^9,SEARCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(""" & Cells(2, "b") & """),""-"",""""),"" "",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2:I1700,""-"",""""),"" "",""""),"""","""")),I2:I1700))")`` HansV
Posts: 74787
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Break Formula

Why are you replacing "" with "" multiple times?
Regards,
Hans HansV
Posts: 74787
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Break Formula

You can shorten the line to

Code: Select all

``x = Evaluate("=IF(""" & Cells(2, "b") & """="""","""",LOOKUP(9^9,SEARCH(SUBSTITUTE(SUBSTITUTE(PROPER(""" & Cells(2, "b") & """),""-"",""""),"" "",""""),SUBSTITUTE(SUBSTITUTE(I2:I1700,""-"",""""),"" "","""")),I2:I1700))")``
Regards,
Hans

3StarLounger
Posts: 211
Joined: 04 Oct 2017, 15:47

### Re: Break Formula

thnx for your reply i removed that IP address to avoid compliance issue from work each substitute have IP address actually(old to replace with new or blank) HansV
Posts: 74787
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Break Formula

Ah - with the URLs, the string you are trying to evaluate is probably longer than 255 characters, and that causes the problem.
You might use a cell to evaluate the formula:

Code: Select all

``````    Range("ZZ1").Formula = "=IF(""" & Cells(2, "b") & """="""","""",LOOKUP(9^9,SEARCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(""" & Cells(2, "b") & """),""-"",""""),"" "",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""",""""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2:I1700,""-"",""""),"" "",""""),"""","""")),I2:I1700))"
x = Range("ZZ1").Value
Range("ZZ1").ClearContents``````
Regards,
Hans

3StarLounger
Posts: 211
Joined: 04 Oct 2017, 15:47

### Re: Break Formula

thnx for idea, this working  max length is 68 character
i mean pieces one line then some part to second line again some part in third line of all formula its also help to shorten view for me otherwise i have to slide right to left. HansV
Posts: 74787
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Break Formula

You can break the line like this"

Code: Select all

``````    Range("ZZ1").Formula = "=IF(""" & Cells(2, "b") & """="""",""""," & _
"LOOKUP(9^9,SEARCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & _
"SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & _
"SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(""" & Cells(2, "b") & _
"""),""-"",""""),"" "",""""),"""",""""),"""",""""),"""",""""),"""",""""),"""","""")," & _
""""",""""),"""",""""),"""",""""),"""",""""),SUBSTITUTE(SUBSTITUTE(" & _
"SUBSTITUTE(I2:I1700,""-"",""""),"" "",""""),"""","""")),I2:I1700))"
``````
but with the URLs it will be different of course.
Regards,
Hans

3StarLounger
Posts: 211
Joined: 04 Oct 2017, 15:47

### Re: Break Formula

thnx for help, its good now does this formation still work in evaluate as formula in post1. HansV
Posts: 74787
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Break Formula

It would work if the total length of the string (including the URLs) was at most 255 characters.
Regards,
Hans

3StarLounger
Posts: 211
Joined: 04 Oct 2017, 15:47

### Re: Break Formula

thnx for fast reply, let me make like this if not work then i will upload file with some dummy data  3StarLounger
Posts: 211
Joined: 04 Oct 2017, 15:47

### Re: Break Formula

i did as you gave but its not worked, i did some experiments

there is total 11 substitute in formula i start added in evaluate formula one by one to know where its stop working(may be some typo mistake from my side) till 9 substitute added its works but when i added 10th substitute its stop working
then for remaining 2 substitute i used another Evaluate then i wrap in lookup formula and its worked

c=evaluate("substitute(B2....till 9
cc=evaluate("substitute(c.....remaining 2

can you endorse that evaluate only contain 9 substitute function in one go

2nd idea which i approached that i removed all substitute and used replace function that took all replace and there is no error with that and worked
like below

Code: Select all

``````sx = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(p, " ", ""), "-", ""), _
"", ""), "", ""), "", ""), _
"", ""), "", ""), "", ""), "", ""), _
"", ""), "", "")``````

3StarLounger
Posts: 211
Joined: 04 Oct 2017, 15:47

### Re: Break Formula

No i wrong below approach is working, there is some glitch in data which turn into error after 9th substitute

Code: Select all

``````Sub exp()
vv = Evaluate("=substitute(substitute(substitute(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(""Apple"",""A"",""""),""Z"",""""),""B"",""""),""C"",""""),""D"",""""),""E"",""""),""F"",""""),""G"",""""),""H"",""""),""PP"",""""),""pp"",""""),""le"","""")")
'vvv = Evaluate("=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(""" & vv & """,""J"",""""),""K"",""""),""L"","""")")
MsgBox vv
End Sub`````` HansV
Posts: 74787
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Break Formula

It doesn't depend on the number of SUBSTITUTEs.
As I mentioned before, Evaluate will fail as soon as the string you try to evaluate becomes longer than 255 characters.
Regards,
Hans

3StarLounger
Posts: 211
Joined: 04 Oct 2017, 15:47

### Re: Break Formula

thanks for your help   i will take of look further
will be back if required many thnx ChrisGreaves
PlutoniumLounger
Posts: 13557
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

### Re: Break Formula

02 Jan 2022, 15:08
how i can break below formula in pieces for make workable
Adeel, I believe that your specific problem has been solved, but as a general principle I try to use any programming language as a means to condense two parameters into one result.
If you imagine (in any language) a formula that combines (a,b,d,e,f,g,h) into a desired result q, then you can write it as a complicated function that takes eight parameters
or
you can write four functions that take two parameters each:-
a,b --> w
c,d --> x
e,f --> y
g,h --> z
Those four functions will be simple, easy to understand in isolation and easy to test in isolation.
Then you can write two more functions that take two parameters each:-
w,x --> m
y,x --> n
Those two functions will be simple, easy to understand in isolation and easy to test in isolation.
Then you can write one more function that takes two parameters:-
m,n --> q

It may seem like a lot of work to write seven functions instead of one function, but my experience is that the elapsed time from idea to result is much shorter.

In a spreadsheet of course, we use columns to calculate those intermediate results (w, x, y, z, m, n) and hide the columns away somewhere.
Cheers
Chris
At two months old I learned to put my foot in my mouth, a skill that has lasted me a lifetime

Hi Chris, thnx for you reply and valuable suggestion , i should consider this 