Incrementing multiple values in a cell

    Hi guys


    I've looked all over and tried a few of my own attempts at a forumal, but nothing is working.


    I have a nextgen gallery and I am trying to change all the galleries to a different display format - nextgen does not offer this ability (which is stupid), which means I need to run a SQL query on about ohh, a thousand galleries to change it in the database. The problem is, is that doing over a thousand of these changes will be cumbersome .. I'd like tot ry and use Excel to do the incremental numbering -but, there are several numbers in each string and I dont know how to make these increment with a formula in excel ...


    UPDATE wp_posts SET post_content = REPLACE(post_content, '[nggallery id=1]', '[nggallery id=1 display_type=photocrati-nextgen_pro_masonry]') WHERE post_content LIKE '[nggallery id=1]';


    desired output


    UPDATE wp_posts SET post_content = REPLACE(post_content, '[nggallery id=2]', '[nggallery id=2 display_type=photocrati-nextgen_pro_masonry]') WHERE post_content LIKE '[nggallery id=2]';
    UPDATE wp_posts SET post_content = REPLACE(post_content, '[nggallery id=3]', '[nggallery id=3 display_type=photocrati-nextgen_pro_masonry]') WHERE post_content LIKE '[nggallery id=3]';
    UPDATE wp_posts SET post_content = REPLACE(post_content, '[nggallery id=4]', '[nggallery id=4 display_type=photocrati-nextgen_pro_masonry]') WHERE post_content LIKE '[nggallery id=4]';



    etc etc. Can someone help with a formula to allow these increments within the string?

    Re: Incrementing multiple values in a cell


    If you put

    Quote

    UPDATE wp_posts SET post_content = REPLACE(post_content, '[nggallery id=1]', '[nggallery id=1 display_type=photocrati-nextgen_pro_masonry]') WHERE post_content LIKE '[nggallery id=1]';


    into A1 then put

    Code
    =CONCATENATE("UPDATE wp_posts SET post_content = REPLACE(post_content, '[nggallery id=",MID(A1,73,1)+1,"]', '[nggallery id=",MID(A1,73,1)+1," display_type=photocrati-nextgen_pro_masonry]') WHERE post_content LIKE '[nggallery id=",MID(A1,73,1)+1,"]';")


    into A2 and fill down it should work.

    Re: Incrementing multiple values in a cell


    Quote from crushdrinker06;771306

    If you put


    into A1 then put

    Code
    =CONCATENATE("UPDATE wp_posts SET post_content = REPLACE(post_content, '[nggallery id=",MID(A1,73,1)+1,"]', '[nggallery id=",MID(A1,73,1)+1," display_type=photocrati-nextgen_pro_masonry]') WHERE post_content LIKE '[nggallery id=",MID(A1,73,1)+1,"]';")


    into A2 and fill down it should work.



    Hi Crushdrinker - thnakyou very much for your assistance! I have no idea how that forumla works as I dont know much about the concatenate one .. but, it looks good!


    I did test it, and it looks like it is veyr veyr close to wroking! however, when it hits row "10" it resets and starts again from "2" - Ive added it below so you can check it out! And again, thankyou. You guys here are like excel magicians haha


    Re: Incrementing multiple values in a cell


    try putting this into A1 and filling down

    Code
    =CONCATENATE("UPDATE wp_posts SET post_content = REPLACE(post_content, '[nggallery id=",ROW(B1),"]', '[nggallery id=",ROW(B1)," display_type=photocrati-nextgen_pro_masonry]') WHERE post_content LIKE '[nggallery id=",ROW(B1),"]';")


    If you don't want the formula in the first row you can do something similar to what is in the attached file.
    forum.ozgrid.com/index.php?attachment/69228/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login