multiple substitutes in a certain cell

[BROOF]

Hey,

currently I have 3-4 substitute formulas that i use to add HTML coding to a set of copy, is there a way i could combine all substitute formuals i have into 1 big formula? Currnetly my substitute formulas have i think 9 different substitutes in them sinice that is the limit in my excel, can that change?

here is an example: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J45,"*size*","<br><br><b>Size</b><br>"),"*style*","<br><br><b>Available Styles</b><br>"),"•","••"),"½","-1/2"),"—","—"),"(","("),")",")"),"–","–")

i have 3-4 of the above formulas which i would like to combine into 1, Possible???

Thanks,
Bryan

[Pete_UK]

Hi Bryan,

You can only increase the nesting limit if you change your Excel version to 2007 or later (up to 64 levels of nesting, compared with 8), so you can't combine those formulae together with what you have. The only way I can think of to reduce it to a single formula would be to set up a user-defined function (UDF) to do it all for you. Then you would just have a single formula, like:

=prepare_html(J45)

Hope this helps.

Pete

[BROOF]

When i created these substitution formulas i was using excel 2003. I have recently been upgraded to excel 2010, BIG DIFFERENCE!!!

The first question I have is where I would need to go in order to increase the level of nesting? or is that automatic with the newer version on excel?

Also i am very interested in the User defined function. if I wanted to set this up where would that be located? Im still feeling around in the dark since 2003 and 2010 are so different cosmetically.

[Pete_UK]

Well, you might want to update your Profile to reflect the version you are using.

If you use File | Save As and save the file as an Excel workbook (i.e. with an .xlsx extension), then close the file and re-open it, you will be able to access all the extra facilities that the later versions provide you with - more columns, rows, colours, sort fields, nesting limits etc., so then you would be able to carry on with nesting of those SUBSTITUTE functions to end up with a single formula (it will be a bit cumbersome, though).

As regards the UDF solution, you would use VBA (built-in to all versions of Excel) to create your own function which would carry out the necessary amendments to the string to convert it into the format you want. You would need to enable macros when you open the file, and you would have to save it as a macro-enabled workbook (.xlsm extension) or as a 97/2003 compatible workbook (.xls extension). The programming code that defines the steps to be taken would be visible in the Visual Basic Editor, which can be brought up by the Alt-F11 keys.

Hope this helps.

Pete

[BROOF]

Oops!! yea completly forgot about changing that, Thanks!

as for the nesting yes i can see that now i can combine more than just the 8 substitions. Yay!

also i will tinker around with the UDF aspect of this to see what i can find as a best practice.

Thanks for your help!