RE: non-VBA Alternative to SUBTITUTE function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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_Header_Color}"><strong>{unique_Left_Column_Header}</strong></font>
<br />
<font
color="{static_Left_Column_and_Photo_Caption_Header_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_Header_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,"<description><![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDIRECT(INDIRECT("Template_"&G19&"_rn")&"!E86"),indirect(I19&"!$F$50"),indirect("PlacemarkData!I"&D19)),indirect(I19&"!$F$51"),indirect("PlacemarkData!J"&D19)),indirect(I19&"!$F$52"),indirect("PlacemarkData!K"&D19)),indirect(I19&"!$F$53"),indirect("PlacemarkData!L"&D19)),indirect(I19&"!$F$54"),indirect("PlacemarkData!M"&D19)),indirect(I19&"!$F$55"),indirect("PlacemarkData!N"&D19)),indirect(I19&"!$F$56"),indirect("PlacemarkData!O"&D19)),indirect(I19&"!$F$57"),indirect("PlacemarkData!P"&D19)),indirect(I19&"!$F$58"),indirect("PlacemarkData!Q"&D19)),indirect(I19&"!$F$59"),indirect("PlacemarkData!R"&D19)),indirect(I19&"!$F$60"),indirect("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)),indirect(I19&"!$F$64"),indirect("PlacemarkData!W"&D19)),indirect(I19&"!$F$65"),indirect("PlacemarkData!X"&D19)),indirect(I19&"!$F$66"),indirect("PlacemarkData!Y"&D19)),indirect(I19&"!$F$67"),indirect("PlacemarkData!Z"&D19)),indirect(I19&"!$F$68"),indirect("PlacemarkData!AA"&D19)),indirect(I19&"!$F$69"),indirect("PlacemarkData!AB"&D19)),indirect(I19&"!$F$70"),indirect("PlacemarkData!AC"&D19)),indirect(I19&"!$F$71"),indirect("PlacemarkData!AD"&D19)),indirect(I19&"!$F$72"),indirect("PlacemarkData!AE"&D19)),indirect(I19&"!$F$73"),indirect("PlacemarkData!AF"&D19)),indirect(I19&"!$F$74"),indirect("PlacemarkData!AG"&D19)),indirect(I19&"!$F$75"),indirect("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)),indirect(I19&"!$F$79"),indirect("PlacemarkData!AL"&D19)),indirect(I19&"!$F$80"),indirect("PlacemarkData!AM"&D19)),indirect(I19&"!$F$81"),indirect("PlacemarkData!AN"&D19)),CHAR(10),"")&"]]></description>","<description><![CDATA["&upgrade_msg_maps&"]]></description>"),""),error_msg_prefix&ADDRESS(D19,COLUMN(),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.
.

Relevant Pages

  • Re: Identical text in two locations
    ... well in Word in such a situation is the StyleRef field which seeks out text ... If all that is going into your table cell is the ... current date) and have that date appear again in my template, ... the cell, I would click between the brackets, type the date, hit Ctrl-A, ...
    (microsoft.public.word.docmanagement)
  • Re: Identical text in two locations
    ... The brackets in the table where marked you bookmark. ... Check Insert>Bookmarks to determine the bookmark name of the cell holding ... > document created from a template I made, in a table cell I want to ...
    (microsoft.public.word.docmanagement)
  • RE: Any sugguestion to set up this Calculation?
    ... I had a table consist of three Country. ... on my template I need to choose a correct country. ... Think of VLOOKUP as a single value, and it can be used in formulas just like ... According to the below sample the 0.65 is a fit rate cell but i need to be ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Any sugguestion to set up this Calculation?
    ... I had a table consist of three Country. ... on my template I need to choose a correct country. ... Think of VLOOKUP as a single value, and it can be used in formulas just like ... According to the below sample the 0.65 is a fit rate cell but i need to be ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Opening a Word template from excel
    ... dot file rather than just the template name in the sub). ... you can make the Word document visible to allow the user to make ... Whenever you click the cell in column A that is immediately below the ... Dim strDocName As String ...
    (microsoft.public.excel.misc)