## Break Formula

2StarLounger
Posts: 186
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: 74188
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: 74188
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

2StarLounger
Posts: 186
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: 74188
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

2StarLounger
Posts: 186
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: 74188
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

2StarLounger
Posts: 186
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: 74188
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

2StarLounger
Posts: 186
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  2StarLounger
Posts: 186
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, " ", ""), "-", ""), _
"", ""), "", ""), "", ""), _
"", ""), "", ""), "", ""), "", ""), _
"", ""), "", "")``````

2StarLounger
Posts: 186
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: 74188
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

2StarLounger
Posts: 186
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: 13282
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
If you think you’re too small to make a difference, try sleeping with a mosquito (Dalai Lama)

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