Break Formula

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Break Formula

Post by adeel1 »

Hi all

how i can break below formula in pieces for make workable

i believe due to single long line its not working

Adeel

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

User avatar
HansV
Administrator
Posts: 74384
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Break Formula

Post by HansV »

Why are you replacing "" with "" multiple times?
Regards,
Hans

User avatar
HansV
Administrator
Posts: 74384
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Break Formula

Post by HansV »

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

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Break Formula

Post by adeel1 »

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)

User avatar
HansV
Administrator
Posts: 74384
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Break Formula

Post by HansV »

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

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Break Formula

Post by adeel1 »

thnx for idea, this working :clapping: :clapping:

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.

Adeel

User avatar
HansV
Administrator
Posts: 74384
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Break Formula

Post by HansV »

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

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Break Formula

Post by adeel1 »

thnx for help, its good now :thankyou:

does this formation still work in evaluate as formula in post1.
Adeel

User avatar
HansV
Administrator
Posts: 74384
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Break Formula

Post by HansV »

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

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Break Formula

Post by adeel1 »

thnx for fast reply, let me make like this if not work then i will upload file with some dummy data :thankyou: :thankyou:

Adeel

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Break Formula

Post by adeel1 »

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, " ", ""), "-", ""), _
            "", ""), "", ""), "", ""), _
              "", ""), "", ""), "", ""), "", ""), _
                 "", ""), "", "")

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Break Formula

Post by adeel1 »

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

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

User avatar
HansV
Administrator
Posts: 74384
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Break Formula

Post by HansV »

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

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Break Formula

Post by adeel1 »

thanks for your help :fanfare: :thankyou: :thankyou: i will take of look further
will be back if required many thnx
Adeel

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 13359
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Break Formula

Post by ChrisGreaves »

adeel1 wrote:
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
Four out of three people have trouble with Fractions

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Break Formula

Post by adeel1 »

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

Adeel