|
non-VBA Alternative to SUBTITUTE function
I have a somewhat complex problem, so I will try to explain with enough
detail what I have here without writing a book about it. I am adapting a large spreadsheet that was not created in excel. Many of the functions work a little differently, so I was not able to simply export and have it work correctly. I have no background in VBA and would prefer to avoid it, as it would take a great deal of debugging, and the purpose of my problem is only datacentric. There do not have to be any moving parts, just data compiled from the workbook and displayed back in one cell. I have managed to adapt almost everything except this one function: What this does is takes a long string of text stored in one cell that acts as template to populate unique values from another sheet creating one long string of text that is unique to a row of data from another sheet. The "import values" are stored in a range just above the template cell, and those values are represented in the template text inside brackets. For example, one variable I will "import" is Name. The word Name is entered in one of the cells in the range above the template cell, lets say A1. The text of the template cell contains {Name}. Like: blahblahblahtexttexttext{Name}blahblahblah This formula currently reports back the template cell, but substitutes in the template cell, the value contained in A1, with the value contained at an address determined by references next to the cell that contains this formula. Here is my problem. I am needing to substitute a lot of values, and the formula is waaay to long. Here is what it looks like, and while it would work, excel will not accept something this long: IFERROR(if(F19,IF(backwards_compatible,"<descripti on<![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(INDIRECT(INDIRECT("Template_"&G19&" _rn")&"!E86"),indirect(I19&"!$F$50"),indirect("Pla cemarkData!I"&D19)),indirect(I19&"!$F$51"),indirec t("PlacemarkData!J"&D19)),indirect(I19&"!$F$52"),i ndirect("PlacemarkData!K"&D19)),indirect(I19&"!$F$ 53"),indirect("PlacemarkData!L"&D19)),indirect(I19 &"!$F$54"),indirect("PlacemarkData!M"&D19)),indire ct(I19&"!$F$55"),indirect("PlacemarkData!N"&D19)), indirect(I19&"!$F$56"),indirect("PlacemarkData!O"& D19)),indirect(I19&"!$F$57"),indirect("PlacemarkDa ta!P"&D19)),indirect(I19&"!$F$58"),indirect("Place markData!Q"&D19)),indirect(I19&"!$F$59"),indirect( "PlacemarkData!R"&D19)),indirect(I19&"!$F$60"),ind irect("PlacemarkData!S"&D19)),indirect(I19&"!$F$61 "),indirect("PlacemarkData!T"&D19)),indirect(I19&" !$F$62"),indirect("PlacemarkData!U"&D19)),indirect (I19&"!$F$63"),indirect("PlacemarkData!V"&D19)),in direct(I19&"!$F$64"),indirect("PlacemarkData!W"&D1 9)),indirect(I19&"!$F$65"),indirect("PlacemarkData !X"&D19)),indirect(I19&"!$F$66"),indirect("Placema rkData!Y"&D19)),indirect(I19&"!$F$67"),indirect("P lacemarkData!Z"&D19)),indirect(I19&"!$F$68"),indir ect("PlacemarkData!AA"&D19)),indirect(I19&"!$F$69" ),indirect("PlacemarkData!AB"&D19)),indirect(I19&" !$F$70"),indirect("PlacemarkData!AC"&D19)),indirec t(I19&"!$F$71"),indirect("PlacemarkData!AD"&D19)), indirect(I19&"!$F$72"),indirect("PlacemarkData!AE" &D19)),indirect(I19&"!$F$73"),indirect("PlacemarkD ata!AF"&D19)),indirect(I19&"!$F$74"),indirect("Pla cemarkData!AG"&D19)),indirect(I19&"!$F$75"),indire ct("PlacemarkData!AH"&D19)),indirect(I19&"!$F$76") ,indirect("PlacemarkData!AI"&D19)),indirect(I19&"! $F$77"),indirect("PlacemarkData!AJ"&D19)),indirect (I19&"!$F$78"),indirect("PlacemarkData!AK"&D19)),i ndirect(I19&"!$F$79"),indirect("PlacemarkData!AL"& D19)),indirect(I19&"!$F$80"),indirect("PlacemarkDa ta!AM"&D19)),indirect(I19&"!$F$81"),indirect("Plac emarkData!AN"&D19)),CHAR(10),"")&"]]</description","<description<![CDATA["&upgrade_msg_maps&"]]</description"),""),error_msg_prefix&ADDRESS(D19,CO LUMN(),4)&error_msg_suffix) The Iferror is one of the symantics I had to fix in the rest of the workbook, I can take care of that, but first I need to figure out a way to organize the data so that I can fit it in a formula. Is this enough info? I can email a copy of the whole document to someone who can help me think through this. |
non-VBA Alternative to SUBTITUTE function
That's a big formula.
If you must use SUBSTITUTE, you can do it in steps using helper columns (or helper worksheets, but I prefer columns). Just do 4 or so (pick a comfortable number that will still work with Excel) at a time. Alternatively, if your goal is hard coded values and you don't care if the formulas remain, you could use Find/Replace in the Edit menu. "Justin Larson" wrote: I have a somewhat complex problem, so I will try to explain with enough detail what I have here without writing a book about it. I am adapting a large spreadsheet that was not created in excel. Many of the functions work a little differently, so I was not able to simply export and have it work correctly. I have no background in VBA and would prefer to avoid it, as it would take a great deal of debugging, and the purpose of my problem is only datacentric. There do not have to be any moving parts, just data compiled from the workbook and displayed back in one cell. I have managed to adapt almost everything except this one function: What this does is takes a long string of text stored in one cell that acts as template to populate unique values from another sheet creating one long string of text that is unique to a row of data from another sheet. The "import values" are stored in a range just above the template cell, and those values are represented in the template text inside brackets. For example, one variable I will "import" is Name. The word Name is entered in one of the cells in the range above the template cell, lets say A1. The text of the template cell contains {Name}. Like: blahblahblahtexttexttext{Name}blahblahblah This formula currently reports back the template cell, but substitutes in the template cell, the value contained in A1, with the value contained at an address determined by references next to the cell that contains this formula. Here is my problem. I am needing to substitute a lot of values, and the formula is waaay to long. Here is what it looks like, and while it would work, excel will not accept something this long: IFERROR(if(F19,IF(backwards_compatible,"<descripti on<![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(INDIRECT(INDIRECT("Template_"&G19&" _rn")&"!E86"),indirect(I19&"!$F$50"),indirect("Pla cemarkData!I"&D19)),indirect(I19&"!$F$51"),indirec t("PlacemarkData!J"&D19)),indirect(I19&"!$F$52"),i ndirect("PlacemarkData!K"&D19)),indirect(I19&"!$F$ 53"),indirect("PlacemarkData!L"&D19)),indirect(I19 &"!$F$54"),indirect("PlacemarkData!M"&D19)),indire ct(I19&"!$F$55"),indirect("PlacemarkData!N"&D19)), indirect(I19&"!$F$56"),indirect("PlacemarkData!O"& D19)),indirect(I19&"!$F$57"),indirect("PlacemarkDa ta!P"&D19)),indirect(I19&"!$F$58"),indirect("Place markData!Q"&D19)),indirect(I19&"!$F$59"),indirect( "PlacemarkData!R"&D19)),indirect(I19&"!$F$60"),ind irect("PlacemarkData!S"&D19)),indirect(I19&"!$F$61 "),indirect("PlacemarkData!T"&D19)),indirect(I19&" !$F$62"),indirect("PlacemarkData!U"&D19)),indirect (I19&"!$F$63"),indirect("PlacemarkData!V"&D19)),in direct(I19&"!$F$64"),indirect("PlacemarkData!W"&D1 9)),indirect(I19&"!$F$65"),indirect("PlacemarkData !X"&D19)),indirect(I19&"!$F$66"),indirect("Placema rkData!Y"&D19)),indirect(I19&"!$F$67"),indirect("P lacemarkData!Z"&D19)),indirect(I19&"!$F$68"),indir ect("PlacemarkData!AA"&D19)),indirect(I19&"!$F$69" ),indirect("PlacemarkData!AB"&D19)),indirect(I19&" !$F$70"),indirect("PlacemarkData!AC"&D19)),indirec t(I19&"!$F$71"),indirect("PlacemarkData!AD"&D19)), indirect(I19&"!$F$72"),indirect("PlacemarkData!AE" &D19)),indirect(I19&"!$F$73"),indirect("PlacemarkD ata!AF"&D19)),indirect(I19&"!$F$74"),indirect("Pla cemarkData!AG"&D19)),indirect(I19&"!$F$75"),indire ct("PlacemarkData!AH"&D19)),indirect(I19&"!$F$76") ,indirect("PlacemarkData!AI"&D19)),indirect(I19&"! $F$77"),indirect("PlacemarkData!AJ"&D19)),indirect (I19&"!$F$78"),indirect("PlacemarkData!AK"&D19)),i ndirect(I19&"!$F$79"),indirect("PlacemarkData!AL"& D19)),indirect(I19&"!$F$80"),indirect("PlacemarkDa ta!AM"&D19)),indirect(I19&"!$F$81"),indirect("Plac emarkData!AN"&D19)),CHAR(10),"")&"]]</description","<description<![CDATA["&upgrade_msg_maps&"]]</description"),""),error_msg_prefix&ADDRESS(D19,CO LUMN(),4)&error_msg_suffix) The Iferror is one of the symantics I had to fix in the rest of the workbook, I can take care of that, but first I need to figure out a way to organize the data so that I can fit it in a formula. Is this enough info? I can email a copy of the whole document to someone who can help me think through this. |
non-VBA Alternative to SUBTITUTE function
I'm not married to SUBSTITUTE, in fact, I'm looking for an alternative that
will do the same thing. In essence, I'm doing a mail merge, only with a single cell in excel. It does need to be dynamic, because this formula exists in 400 cells, and creates a list of unique values that eventually leads to a .txt output that can be read by a map (.kml). One thing that crossed my mind, but haven't finished testing yet, is pasting the whole formula, without the = at the beginning in [celladdressx], and replacing all the cell references in it with indirect("x"&row()), and then using a simple Indirect([celladdressx]) where the long formula was to calculate it. Is there a limit on length of calculation when it's done through an indirect? Experimenting. "sb1920alk" wrote: That's a big formula. If you must use SUBSTITUTE, you can do it in steps using helper columns (or helper worksheets, but I prefer columns). Just do 4 or so (pick a comfortable number that will still work with Excel) at a time. Alternatively, if your goal is hard coded values and you don't care if the formulas remain, you could use Find/Replace in the Edit menu. "Justin Larson" wrote: I have a somewhat complex problem, so I will try to explain with enough detail what I have here without writing a book about it. I am adapting a large spreadsheet that was not created in excel. Many of the functions work a little differently, so I was not able to simply export and have it work correctly. I have no background in VBA and would prefer to avoid it, as it would take a great deal of debugging, and the purpose of my problem is only datacentric. There do not have to be any moving parts, just data compiled from the workbook and displayed back in one cell. I have managed to adapt almost everything except this one function: What this does is takes a long string of text stored in one cell that acts as template to populate unique values from another sheet creating one long string of text that is unique to a row of data from another sheet. The "import values" are stored in a range just above the template cell, and those values are represented in the template text inside brackets. For example, one variable I will "import" is Name. The word Name is entered in one of the cells in the range above the template cell, lets say A1. The text of the template cell contains {Name}. Like: blahblahblahtexttexttext{Name}blahblahblah This formula currently reports back the template cell, but substitutes in the template cell, the value contained in A1, with the value contained at an address determined by references next to the cell that contains this formula. Here is my problem. I am needing to substitute a lot of values, and the formula is waaay to long. Here is what it looks like, and while it would work, excel will not accept something this long: IFERROR(if(F19,IF(backwards_compatible,"<descripti on<![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(INDIRECT(INDIRECT("Template_"&G19&" _rn")&"!E86"),indirect(I19&"!$F$50"),indirect("Pla cemarkData!I"&D19)),indirect(I19&"!$F$51"),indirec t("PlacemarkData!J"&D19)),indirect(I19&"!$F$52"),i ndirect("PlacemarkData!K"&D19)),indirect(I19&"!$F$ 53"),indirect("PlacemarkData!L"&D19)),indirect(I19 &"!$F$54"),indirect("PlacemarkData!M"&D19)),indire ct(I19&"!$F$55"),indirect("PlacemarkData!N"&D19)), indirect(I19&"!$F$56"),indirect("PlacemarkData!O"& D19)),indirect(I19&"!$F$57"),indirect("PlacemarkDa ta!P"&D19)),indirect(I19&"!$F$58"),indirect("Place markData!Q"&D19)),indirect(I19&"!$F$59"),indirect( "PlacemarkData!R"&D19)),indirect(I19&"!$F$60"),ind irect("PlacemarkData!S"&D19)),indirect(I19&"!$F$61 "),indirect("PlacemarkData!T"&D19)),indirect(I19&" !$F$62"),indirect("PlacemarkData!U"&D19)),indirect (I19&"!$F$63"),indirect("PlacemarkData!V"&D19)),in direct(I19&"!$F$64"),indirect("PlacemarkData!W"&D1 9)),indirect(I19&"!$F$65"),indirect("PlacemarkData !X"&D19)),indirect(I19&"!$F$66"),indirect("Placema rkData!Y"&D19)),indirect(I19&"!$F$67"),indirect("P lacemarkData!Z"&D19)),indirect(I19&"!$F$68"),indir ect("PlacemarkData!AA"&D19)),indirect(I19&"!$F$69" ),indirect("PlacemarkData!AB"&D19)),indirect(I19&" !$F$70"),indirect("PlacemarkData!AC"&D19)),indirec t(I19&"!$F$71"),indirect("PlacemarkData!AD"&D19)), indirect(I19&"!$F$72"),indirect("PlacemarkData!AE" &D19)),indirect(I19&"!$F$73"),indirect("PlacemarkD ata!AF"&D19)),indirect(I19&"!$F$74"),indirect("Pla cemarkData!AG"&D19)),indirect(I19&"!$F$75"),indire ct("PlacemarkData!AH"&D19)),indirect(I19&"!$F$76") ,indirect("PlacemarkData!AI"&D19)),indirect(I19&"! $F$77"),indirect("PlacemarkData!AJ"&D19)),indirect (I19&"!$F$78"),indirect("PlacemarkData!AK"&D19)),i ndirect(I19&"!$F$79"),indirect("PlacemarkData!AL"& D19)),indirect(I19&"!$F$80"),indirect("PlacemarkDa ta!AM"&D19)),indirect(I19&"!$F$81"),indirect("Plac emarkData!AN"&D19)),CHAR(10),"")&"]]</description","<description<![CDATA["&upgrade_msg_maps&"]]</description"),""),error_msg_prefix&ADDRESS(D19,CO LUMN(),4)&error_msg_suffix) The Iferror is one of the symantics I had to fix in the rest of the workbook, I can take care of that, but first I need to figure out a way to organize the data so that I can fit it in a formula. Is this enough info? I can email a copy of the whole document to someone who can help me think through this. |
non-VBA Alternative to SUBTITUTE function
Could you post an example of what you're starting with and what you want to
end up with? Perhaps I can help you better if I can see it. "Justin Larson" wrote: I'm not married to SUBSTITUTE, in fact, I'm looking for an alternative that will do the same thing. In essence, I'm doing a mail merge, only with a single cell in excel. It does need to be dynamic, because this formula exists in 400 cells, and creates a list of unique values that eventually leads to a .txt output that can be read by a map (.kml). One thing that crossed my mind, but haven't finished testing yet, is pasting the whole formula, without the = at the beginning in [celladdressx], and replacing all the cell references in it with indirect("x"&row()), and then using a simple Indirect([celladdressx]) where the long formula was to calculate it. Is there a limit on length of calculation when it's done through an indirect? Experimenting. "sb1920alk" wrote: That's a big formula. If you must use SUBSTITUTE, you can do it in steps using helper columns (or helper worksheets, but I prefer columns). Just do 4 or so (pick a comfortable number that will still work with Excel) at a time. Alternatively, if your goal is hard coded values and you don't care if the formulas remain, you could use Find/Replace in the Edit menu. "Justin Larson" wrote: I have a somewhat complex problem, so I will try to explain with enough detail what I have here without writing a book about it. I am adapting a large spreadsheet that was not created in excel. Many of the functions work a little differently, so I was not able to simply export and have it work correctly. I have no background in VBA and would prefer to avoid it, as it would take a great deal of debugging, and the purpose of my problem is only datacentric. There do not have to be any moving parts, just data compiled from the workbook and displayed back in one cell. I have managed to adapt almost everything except this one function: What this does is takes a long string of text stored in one cell that acts as template to populate unique values from another sheet creating one long string of text that is unique to a row of data from another sheet. The "import values" are stored in a range just above the template cell, and those values are represented in the template text inside brackets. For example, one variable I will "import" is Name. The word Name is entered in one of the cells in the range above the template cell, lets say A1. The text of the template cell contains {Name}. Like: blahblahblahtexttexttext{Name}blahblahblah This formula currently reports back the template cell, but substitutes in the template cell, the value contained in A1, with the value contained at an address determined by references next to the cell that contains this formula. Here is my problem. I am needing to substitute a lot of values, and the formula is waaay to long. Here is what it looks like, and while it would work, excel will not accept something this long: IFERROR(if(F19,IF(backwards_compatible,"<descripti on<![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(INDIRECT(INDIRECT("Template_"&G19&" _rn")&"!E86"),indirect(I19&"!$F$50"),indirect("Pla cemarkData!I"&D19)),indirect(I19&"!$F$51"),indirec t("PlacemarkData!J"&D19)),indirect(I19&"!$F$52"),i ndirect("PlacemarkData!K"&D19)),indirect(I19&"!$F$ 53"),indirect("PlacemarkData!L"&D19)),indirect(I19 &"!$F$54"),indirect("PlacemarkData!M"&D19)),indire ct(I19&"!$F$55"),indirect("PlacemarkData!N"&D19)), indirect(I19&"!$F$56"),indirect("PlacemarkData!O"& D19)),indirect(I19&"!$F$57"),indirect("PlacemarkDa ta!P"&D19)),indirect(I19&"!$F$58"),indirect("Place markData!Q"&D19)),indirect(I19&"!$F$59"),indirect( "PlacemarkData!R"&D19)),indirect(I19&"!$F$60"),ind irect("PlacemarkData!S"&D19)),indirect(I19&"!$F$61 "),indirect("PlacemarkData!T"&D19)),indirect(I19&" !$F$62"),indirect("PlacemarkData!U"&D19)),indirect (I19&"!$F$63"),indirect("PlacemarkData!V"&D19)),in direct(I19&"!$F$64"),indirect("PlacemarkData!W"&D1 9)),indirect(I19&"!$F$65"),indirect("PlacemarkData !X"&D19)),indirect(I19&"!$F$66"),indirect("Placema rkData!Y"&D19)),indirect(I19&"!$F$67"),indirect("P lacemarkData!Z"&D19)),indirect(I19&"!$F$68"),indir ect("PlacemarkData!AA"&D19)),indirect(I19&"!$F$69" ),indirect("PlacemarkData!AB"&D19)),indirect(I19&" !$F$70"),indirect("PlacemarkData!AC"&D19)),indirec t(I19&"!$F$71"),indirect("PlacemarkData!AD"&D19)), indirect(I19&"!$F$72"),indirect("PlacemarkData!AE" &D19)),indirect(I19&"!$F$73"),indirect("PlacemarkD ata!AF"&D19)),indirect(I19&"!$F$74"),indirect("Pla cemarkData!AG"&D19)),indirect(I19&"!$F$75"),indire ct("PlacemarkData!AH"&D19)),indirect(I19&"!$F$76") ,indirect("PlacemarkData!AI"&D19)),indirect(I19&"! $F$77"),indirect("PlacemarkData!AJ"&D19)),indirect (I19&"!$F$78"),indirect("PlacemarkData!AK"&D19)),i ndirect(I19&"!$F$79"),indirect("PlacemarkData!AL"& D19)),indirect(I19&"!$F$80"),indirect("PlacemarkDa ta!AM"&D19)),indirect(I19&"!$F$81"),indirect("Plac emarkData!AN"&D19)),CHAR(10),"")&"]]</description","<description<![CDATA["&upgrade_msg_maps&"]]</description"),""),error_msg_prefix&ADDRESS(D19,CO LUMN(),4)&error_msg_suffix) The Iferror is one of the symantics I had to fix in the rest of the workbook, I can take care of that, but first I need to figure out a way to organize the data so that I can fit it in a formula. Is this enough info? I can email a copy of the whole document to someone who can help me think through this. |
non-VBA Alternative to SUBTITUTE function
Here's the text in the template, in which I am replacing values inside the
brackets. the values that I am replacing from the brackets come from different sheets depending on the value of cells to the right of my massive equation, hence the multiple indirect functions. Anyhoo, anything below inside {} is a value that is being looked up and replaced. <table width="{static_Balloon_Width}" cellpadding="0" cellspacing="0" <tr <td colspan="3" align="left" valign="top" <table width="100%" cellpadding="0" cellspacing="0" <tr <td align="left" valign="middle" <img src="{static_Logo_URL}" width="{static_Logo_Width}" / </td <td align="left" valign="middle" <font color="{static_Organization_Text_Color}" size="+2"<strong{static_Organization}</strong</font <hr / </td </tr </table </td </tr <tr <td align="left" valign="top" <p <font color="{static_Left_Column_and_Photo_Caption_Heade r_Color}"<strong{unique_Left_Column_Header}</strong</font <br / <font color="{static_Left_Column_and_Photo_Caption_Heade r_Color}"{unique_Left_Column_Text}</font </p <p <a href="{unique_Read_More_Link_URL}" target="_blank"<strong<font color="{static_Link_Text_Color}"{unique_Read_More _Link_Text}</font</strong</a <br / <a href="{static_Org_Link_URL}" target="_blank"<strong<font color="{static_Link_Text_Color}"{static_Org_Link_ Text}</font</strong</a </p </td <td width="10" align="left" valign="top" </td <td align="right" valign="top" <table border="0" cellspacing="0" cellpadding="0" bgcolor="white" <tr <td align="center" <img src="{unique_Photo_URL}" width="{static_Photo_Width}" align="left" / </td </tr <tr <td align="left" valign="top" <dl <dt<font color="{static_Left_Column_and_Photo_Caption_Heade r_Color}"<strong{unique_Photo_Title}</strong</font</dt <dd<font color="{static_Photo_Caption_Text_Color}"{unique_ Photo_Caption_Text} (photo: {unique_Photo_Credit})</font</dd </dl </td </tr </table </td </tr <tr <td colspan="3" align="right" valign="top" <font color="{static_Text_Color}"{static_Footer_Text}</font </td </tr </table "sb1920alk" wrote: Could you post an example of what you're starting with and what you want to end up with? Perhaps I can help you better if I can see it. "Justin Larson" wrote: I'm not married to SUBSTITUTE, in fact, I'm looking for an alternative that will do the same thing. In essence, I'm doing a mail merge, only with a single cell in excel. It does need to be dynamic, because this formula exists in 400 cells, and creates a list of unique values that eventually leads to a .txt output that can be read by a map (.kml). One thing that crossed my mind, but haven't finished testing yet, is pasting the whole formula, without the = at the beginning in [celladdressx], and replacing all the cell references in it with indirect("x"&row()), and then using a simple Indirect([celladdressx]) where the long formula was to calculate it. Is there a limit on length of calculation when it's done through an indirect? Experimenting. "sb1920alk" wrote: That's a big formula. If you must use SUBSTITUTE, you can do it in steps using helper columns (or helper worksheets, but I prefer columns). Just do 4 or so (pick a comfortable number that will still work with Excel) at a time. Alternatively, if your goal is hard coded values and you don't care if the formulas remain, you could use Find/Replace in the Edit menu. "Justin Larson" wrote: I have a somewhat complex problem, so I will try to explain with enough detail what I have here without writing a book about it. I am adapting a large spreadsheet that was not created in excel. Many of the functions work a little differently, so I was not able to simply export and have it work correctly. I have no background in VBA and would prefer to avoid it, as it would take a great deal of debugging, and the purpose of my problem is only datacentric. There do not have to be any moving parts, just data compiled from the workbook and displayed back in one cell. I have managed to adapt almost everything except this one function: What this does is takes a long string of text stored in one cell that acts as template to populate unique values from another sheet creating one long string of text that is unique to a row of data from another sheet. The "import values" are stored in a range just above the template cell, and those values are represented in the template text inside brackets. For example, one variable I will "import" is Name. The word Name is entered in one of the cells in the range above the template cell, lets say A1. The text of the template cell contains {Name}. Like: blahblahblahtexttexttext{Name}blahblahblah This formula currently reports back the template cell, but substitutes in the template cell, the value contained in A1, with the value contained at an address determined by references next to the cell that contains this formula. Here is my problem. I am needing to substitute a lot of values, and the formula is waaay to long. Here is what it looks like, and while it would work, excel will not accept something this long: IFERROR(if(F19,IF(backwards_compatible,"<descripti on<![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(INDIRECT(INDIRECT("Template_"&G19&" _rn")&"!E86"),indirect(I19&"!$F$50"),indirect("Pla cemarkData!I"&D19)),indirect(I19&"!$F$51"),indirec t("PlacemarkData!J"&D19)),indirect(I19&"!$F$52"),i ndirect("PlacemarkData!K"&D19)),indirect(I19&"!$F$ 53"),indirect("PlacemarkData!L"&D19)),indirect(I19 &"!$F$54"),indirect("PlacemarkData!M"&D19)),indire ct(I19&"!$F$55"),indirect("PlacemarkData!N"&D19)), indirect(I19&"!$F$56"),indirect("PlacemarkData!O"& D19)),indirect(I19&"!$F$57"),indirect("PlacemarkDa ta!P"&D19)),indirect(I19&"!$F$58"),indirect("Place markData!Q"&D19)),indirect(I19&"!$F$59"),indirect( "PlacemarkData!R"&D19)),indirect(I19&"!$F$60"),ind irect("PlacemarkData!S"&D19)),indirect(I19&"!$F$61 "),indirect("PlacemarkData!T"&D19)),indirect(I19&" !$F$62"),indirect("PlacemarkData!U"&D19)),indirect (I19&"!$F$63"),indirect("PlacemarkData!V"&D19)),in direct(I19&"!$F$64"),indirect("PlacemarkData!W"&D1 9)),indirect(I19&"!$F$65"),indirect("PlacemarkData !X"&D19)),indirect(I19&"!$F$66"),indirect("Placema rkData!Y"&D19)),indirect(I19&"!$F$67"),indirect("P lacemarkData!Z"&D19)),indirect(I19&"!$F$68"),indir ect("PlacemarkData!AA"&D19)),indirect(I19&"!$F$69" ),indirect("PlacemarkData!AB"&D19)),indirect(I19&" !$F$70"),indirect("PlacemarkData!AC"&D19)),indirec t(I19&"!$F$71"),indirect("PlacemarkData!AD"&D19)), indirect(I19&"!$F$72"),indirect("PlacemarkData!AE" &D19)),indirect(I19&"!$F$73"),indirect("PlacemarkD ata!AF"&D19)),indirect(I19&"!$F$74"),indirect("Pla cemarkData!AG"&D19)),indirect(I19&"!$F$75"),indire ct("PlacemarkData!AH"&D19)),indirect(I19&"!$F$76") ,indirect("PlacemarkData!AI"&D19)),indirect(I19&"! $F$77"),indirect("PlacemarkData!AJ"&D19)),indirect (I19&"!$F$78"),indirect("PlacemarkData!AK"&D19)),i ndirect(I19&"!$F$79"),indirect("PlacemarkData!AL"& D19)),indirect(I19&"!$F$80"),indirect("PlacemarkDa ta!AM"&D19)),indirect(I19&"!$F$81"),indirect("Plac emarkData!AN"&D19)),CHAR(10),"")&"]]</description","<description<![CDATA["&upgrade_msg_maps&"]]</description"),""),error_msg_prefix&ADDRESS(D19,CO LUMN(),4)&error_msg_suffix) The Iferror is one of the symantics I had to fix in the rest of the workbook, I can take care of that, but first I need to figure out a way to organize the data so that I can fit it in a formula. Is this enough info? I can email a copy of the whole document to someone who can help me think through this. |
non-VBA Alternative to SUBTITUTE function
Ok, this should be simple enough. Let me make sure I've got this right:
You're using Excel to produce HTML output and you have 21 (by my count) unique variables that will populate the your code: {static_Balloon_Width} {static_Logo_URL} {static_Logo_Width} {static_Organization_Text_Color} {static_Organization} {static_Left_Column_and_Photo_Caption_Header_Color } {unique_Left_Column_Header} {unique_Left_Column_Text} {unique_Read_More_Link_URL} {static_Link_Text_Color} {unique_Read_More_Link_Text {static_Org_Link_URL} {static_Org_Link_Text} {unique_Photo_URL} {static_Photo_Width} {unique_Photo_Title} {static_Photo_Caption_Text_Color} {unique_Photo_Caption_Text} {unique_Photo_Credit} {static_Text_Color} {static_Footer_Text} I put the "big block of code" in A2. In B1:V1(ish) are the values that will replace the variables in B3:V3(ish) that will populate the template. So your workbook will need to reference the source book in B1:V1 and have {static_Balloon_Width} in B3, {static_Logo_URL} in C3, etc, in this example. Put =SUBSTITUTE(A2,B3,B1) in B2 and pull it left to column V(ish). And V2 will contain the fully populated code. IMPORTANT NOTE: when copying HTML code from Excel, you cannot paste straight into Notepad (or many other programs) because it screws up the quotes. You have to paste into Word first, and then copy again from Word and paste to your destination. "Justin Larson" wrote: I have a somewhat complex problem, so I will try to explain with enough detail what I have here without writing a book about it. I am adapting a large spreadsheet that was not created in excel. Many of the functions work a little differently, so I was not able to simply export and have it work correctly. I have no background in VBA and would prefer to avoid it, as it would take a great deal of debugging, and the purpose of my problem is only datacentric. There do not have to be any moving parts, just data compiled from the workbook and displayed back in one cell. I have managed to adapt almost everything except this one function: What this does is takes a long string of text stored in one cell that acts as template to populate unique values from another sheet creating one long string of text that is unique to a row of data from another sheet. The "import values" are stored in a range just above the template cell, and those values are represented in the template text inside brackets. For example, one variable I will "import" is Name. The word Name is entered in one of the cells in the range above the template cell, lets say A1. The text of the template cell contains {Name}. Like: blahblahblahtexttexttext{Name}blahblahblah This formula currently reports back the template cell, but substitutes in the template cell, the value contained in A1, with the value contained at an address determined by references next to the cell that contains this formula. Here is my problem. I am needing to substitute a lot of values, and the formula is waaay to long. Here is what it looks like, and while it would work, excel will not accept something this long: IFERROR(if(F19,IF(backwards_compatible,"<descripti on<![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(INDIRECT(INDIRECT("Template_"&G19&" _rn")&"!E86"),indirect(I19&"!$F$50"),indirect("Pla cemarkData!I"&D19)),indirect(I19&"!$F$51"),indirec t("PlacemarkData!J"&D19)),indirect(I19&"!$F$52"),i ndirect("PlacemarkData!K"&D19)),indirect(I19&"!$F$ 53"),indirect("PlacemarkData!L"&D19)),indirect(I19 &"!$F$54"),indirect("PlacemarkData!M"&D19)),indire ct(I19&"!$F$55"),indirect("PlacemarkData!N"&D19)), indirect(I19&"!$F$56"),indirect("PlacemarkData!O"& D19)),indirect(I19&"!$F$57"),indirect("PlacemarkDa ta!P"&D19)),indirect(I19&"!$F$58"),indirect("Place markData!Q"&D19)),indirect(I19&"!$F$59"),indirect( "PlacemarkData!R"&D19)),indirect(I19&"!$F$60"),ind irect("PlacemarkData!S"&D19)),indirect(I19&"!$F$61 "),indirect("PlacemarkData!T"&D19)),indirect(I19&" !$F$62"),indirect("PlacemarkData!U"&D19)),indirect (I19&"!$F$63"),indirect("PlacemarkData!V"&D19)),in direct(I19&"!$F$64"),indirect("PlacemarkData!W"&D1 9)),indirect(I19&"!$F$65"),indirect("PlacemarkData !X"&D19)),indirect(I19&"!$F$66"),indirect("Placema rkData!Y"&D19)),indirect(I19&"!$F$67"),indirect("P lacemarkData!Z"&D19)),indirect(I19&"!$F$68"),indir ect("PlacemarkData!AA"&D19)),indirect(I19&"!$F$69" ),indirect("PlacemarkData!AB"&D19)),indirect(I19&" !$F$70"),indirect("PlacemarkData!AC"&D19)),indirec t(I19&"!$F$71"),indirect("PlacemarkData!AD"&D19)), indirect(I19&"!$F$72"),indirect("PlacemarkData!AE" &D19)),indirect(I19&"!$F$73"),indirect("PlacemarkD ata!AF"&D19)),indirect(I19&"!$F$74"),indirect("Pla cemarkData!AG"&D19)),indirect(I19&"!$F$75"),indire ct("PlacemarkData!AH"&D19)),indirect(I19&"!$F$76") ,indirect("PlacemarkData!AI"&D19)),indirect(I19&"! $F$77"),indirect("PlacemarkData!AJ"&D19)),indirect (I19&"!$F$78"),indirect("PlacemarkData!AK"&D19)),i ndirect(I19&"!$F$79"),indirect("PlacemarkData!AL"& D19)),indirect(I19&"!$F$80"),indirect("PlacemarkDa ta!AM"&D19)),indirect(I19&"!$F$81"),indirect("Plac emarkData!AN"&D19)),CHAR(10),"")&"]]</description","<description<![CDATA["&upgrade_msg_maps&"]]</description"),""),error_msg_prefix&ADDRESS(D19,CO LUMN(),4)&error_msg_suffix) The Iferror is one of the symantics I had to fix in the rest of the workbook, I can take care of that, but first I need to figure out a way to organize the data so that I can fit it in a formula. Is this enough info? I can email a copy of the whole document to someone who can help me think through this. |
non-VBA Alternative to SUBTITUTE function
While I experiment with this, you can look at an original of the spreadsheet
on google docs: http://spreadsheets.google.com/pub?k...rKdAbagiqJ6IYw I'm not sure, but you probably have to sign up for a free account to view it. It's shared though, anyone should be able to open it and look |
| All times are GMT +1. The time now is 03:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com