Break Formula

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

[HansV]

Why are you replacing "" with "" multiple times?

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

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

[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

[adeel1]

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.

Adeel

[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.

[adeel1]

thnx for help, its good now

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

[HansV]

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

[adeel1]

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

Adeel

[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]

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

[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.

[adeel1]

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

[ChrisGreaves]

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

[adeel1]

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

Adeel