Break Formula

adeel1
3StarLounger
Posts: 264
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: 78235
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?
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
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))")
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
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: 78235
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
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
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: 78235
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.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
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: 78235
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.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
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: 264
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: 264
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: 78235
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.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
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: 15498
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
An expensive day out: Wallet and Grimace

adeel1
3StarLounger
Posts: 264
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