Blog

  • http://www.freepeople.com Devin Concannon

    This is GENIUS! Thank you for just saving me countless hours…

  • http://www.analistaseo.es Natzir

    This post is AWESOME! thanks a lot Brett

  • Mike G

    Wow. This is gold. Thank you!

  • http://www.choomka.co.uk Steve at Choomka

    Thanks for the article, for some reason using Excel for creating unique descriptions like this hasn’t really crossed my mind before, strange as I use Excel pretty much daily for managing product data/csvs. Going to give this a go though, I love how expandable it it, previously I was purchasing unique descriptions from content writers, this could potentially save a fair bit of money/time though and I can no doubt rejig the descriptions I already have!

    Pretty valuable, now I just need to find a little time to implement it! :)

  • Brett Snyder

    @Devin @Natzir @Mike Thanks so much! Glad this can save you some time!

    @Steve I don’t think you’re alone there, I even forget sometimes that Excel is much more than just a numbers beast :-)

  • http://www.momentumseo.com Hawaii SEO

    It never ceases to amaze be the things you can do with Excel. Way to push Excel to it’s limits.

  • Tamsin M

    “I was not able to find a way to substitute both variables from the same cell, which is why it required multiple steps.”

    Can’t you just nest the substitutes?
    =SUBSTITUTE(SUBSTITUTE(I2,”[Product]“,A2),”[Category]“,B2)

    For the whole thing in one cell:
    =SUBSTITUTE(SUBSTITUTE(CONCATENATE((INDEX(Sentences!$C$4:$G$4,1,TRUNC((5*RAND())+1))),” “,(INDEX(Sentences!$C$6:$G$6,1,TRUNC((5*RAND())+1))),” “,(INDEX(Sentences!$C$5:$G$5,1,TRUNC((5*RAND())+1))),” “,(CONCATENATE(“The product fits the following themes: “,C2,”, “,D2,” and “,E2,”. “)),” “,(INDEX(Sentences!$H$5:$L$5,1,TRUNC((5*RAND())+1))),” “,(CONCATENATE(“Manufactured by “,F2,”. “)),(CONCATENATE(“[Product] comprised of “,G2,”. “)),(CONCATENATE(“Available in “,H2,”. “)),(INDEX(Sentences!$C$7:$G$7,1,TRUNC((5*RAND())+1)))),”[Product]“,A2),”[Category]“,B2)

  • http://www.lumina.fr Luminaire

    Great post! Thank you for sharing such a helpful method.

  • http://martokus.blogspot.com/ Martokus

    I’ve used same principle in the past but for generating website product descriptions. Used a software called Parabuilder (no longer existing I think) that besides rotating sentences also shuffles the words of each sentence. I prefer it to Excel because it’s web based and you just call it via your program and it returns a set of content.

  • http://adaptise.com Jeff

    Just when you think you’ve seen every little technique in Excel, you come up with this! Awesome!
    Definitely going to use this in the future for the larger sites with too many duplicate (or non existent) meta info.

  • gotcha5832

    Pretty impressive, but what don’t you did it by php?

  • http://www.blueglass.com Annie Cushing

    Wow! These are some awesome formulas – and even better strategy! Well done!

    And thanks for the shout out. :)

  • Brett Snyder

    @Hawaii @Luminaire @Jeff Thanks for the kind words!

    @Martokus I’m not familiar with Parabuilder but you must be right that it doesn’t exist anymore b/c I wasn’t really able to find much on it. I’ll keep looking though, thanks for the tip!

    @gotcha I’m not much of a programmer so not really versed in creating things like this for PHP…I just knew I had a problem and wanted a solution in the best tool I knew how :-)

  • Brett Snyder

    @Tamsin BRILLIANT! I was playing around with nested substitutes but I couldn’t quite get it to work right. Thank you SO much for the tip, going to add it to this post right now!

  • http://perderepesovelocemente.org Carlo Branca

    Wow, i still have not the need to make a e’commerce website so big,
    but your solution it’s great to remember when it will be the right moment.
    I loved also more the part where You say that can be apllyable to metadescription and that it’s a part i will test for a client for sure. Thank You!

  • http://www.lumina.fr Luminaire

    @Brett I tried to create a tool like yours with excel but I didn’t see how to manage with the “concatenate” function coupled with the “random” one.

    It seems easy when you give all the formula;

  • http://www.seerinteractive.com/ Brett

    @Carlo I’m glad you’ll be able to use it with your clients!

    @Luminaire Appreciate that, that’s why I wanted to give each step as well so if you want to construct it piece by piece you’ve got all the tools!

  • http://www.pinehearst.co.uk Cole Whitelaw

    I would abstract that formula one step further.

    You’re hardcoding in that you have 5 sentences which means that you have a lot of formulae to change if you want more variety in the future.

    so instead of =INDEX(Sentences!$C$6:$G$6,1,TRUNC((5*RAND())+1))

    I’d extend the ranges to the end of the sheet and use COUNTA() to see how many actual sentences there are in there – that way you could have differing numbers of intro, body or finals or just more.

    =INDEX(Sentences!$6:$6,1,TRUNC((COUNTA($6:$6)*(RAND())+1)))

  • http://www.seerinteractive.com/ Brett

    @Cole Love the thinking here and taking it to the next level! I agree it makes perfect sense and allows it to be more fluid for building it out, thanks!

  • http://www.morphodesigns.com Mitchell Allen

    Brett, this is a totally cool way to generate semi-random content. I’ve used Excel to generate everything from HTML to Sudoku. However, I would have been partial to importing the whole kit and kaboodle into Microsoft Access. Here’s why:

    Once you’ve discovered how convennient this is, you’re going to want to tweak out the bits you DON’T like about the concatenation schema. Excel’s row,column limitation stifles creativity. Instead of thinking about randomness, context and other content-relevant issues, you have to expand columns, update formulas and consider using named arrays to simplify those pesky references.

    With Access – or any database application, you can take advantage of queries and look-up tables. This means that your choices don’t have to fit in an MxN array. You could have 100 introductory sentences – which is really the meat of this varied content project. The other random fields could be enlarged over time. (Cole Whitelaw’s suggestion, Access-style!)

    But here is the best part. You can sub-divide your sentence groups, making even more granular randomness. Remember Mad-libs? Let’s take “Category Sentences”, for example. You could fine-tune things by creating multiple random choices for this snippet:

    All your [Category] friends will be [adjective].

    By creating a list of adjectives, you’ve opened up more variety. Personally, that’s just the beginning, as people can sense Mad-lib substitutions. Someone mentioned Parabuilder in the context of word order. This would be quite simple to do in Access, as long as you keep the basic sentence structure clear.

    Of course, if this were an ad hoc quick-fix, I would definitely use Excel. :)

    Cheers,

    Mitch

  • http://gnawmedia.com David

    Some great tips here thanks a lot. Always on the look out for ways to make things quicker with excel.

  • msaez

    Superb! Efficiency above all…and long live Excel!

  • http://www.seerinteractive.com/ Brett

    Thank you both, appreciate it!

  • http://www.optilead.co.uk Rich Norton

    Oh wow, that is quite incredible. Excel is a very flexible tool which I am still not entirely on top of.

    You claims that you are unfamiliar with coding, but a lot of the Excel formula you posted looks a great deal like ugly Actionscript 2.0. From the little I know of Excel’s formula system it seems very similar syntactically to some of the cruder, less flexible coding languages.

    Maybe you’d find coding more familiar than you suspect?

    Thanks,

    Rich

  • Hussain

    The formula is not working for me…! seriously need ur help! I am sure that I have make some mistake! Would love to get ur help in this regard!

  • Hussain

    Oh thnx, I got it :)

  • http://www.luminaire-enfant.lumina.fr Luminaire enfant

    Hi,

    The worksheet is no more downloadable ?!?

    How can I retrieve it ?

  • http://www.luminaire-enfant.lumina.fr Luminaire enfant

    Could anyone {give|send} me a link to retrieve the worksheet please ? (@Hussain @Rich @David …)

    Thanks !!

  • Segma

    Hey Luminaire enfant, have you found the worksheet please ? Need help…

  • Segma

    Brett, please, is it possible to {give|send} a link to retrieve the worksheet ? I could be very nice !!!

  • Khalid Seo

    Hi the post is awesome , in fact i am in search of such a tuto for long time , but i am very fresh to excel formula so can anyone please @c935cc08176d2f5916c908841fe9c26d:disqus @1b39562215d1a431950726769bd8e327:disqus @205c582426a7285495fcf481261ec2f9:disqus send me a copie of the worksheet to my email : khalidcoreferencement@gmail.com , really i am in need of it ,,,,,,Thank in advance Brs

  • Jonathan O’Brien

    The links to download the spreadsheet are no longer hyperlinks and therefore I can’t download the sheet. Where can I download the excel sheet?

  • David

    can anyone send me the link of the worksheet please ? my e-mail is business@izz.com.au

  • alucid

    Hi, could anybody send me on the worksheet plz? email : chuckyrus@gmail.com

  • alucid
  • nutrizionista firenze

    real incredible system…

  • Sander

    Hi there, perhaps a bit old blog post, but for me very relevant today. Is there any possibility this worksheet could be emailed towards me ? cimst10@gmail.com. I just spend hours, trying to build my own, and I keep getting, errors. A best practice like one in this post would be very helpful. Greetings from Holland.Thanks in advance!

Get our Newsletter

Keep up-to-date search trends, latest blog posts and more!