non-VBA Alternative to SUBTITUTE function

J

Justin Larson

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.
 
S

sb1920alk

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.
 
J

Justin Larson

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.
 
S

sb1920alk

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
J

Justin Larson

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 said:
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 said:
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 said:
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.

:

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:
Click to expand...
Click to expand...
Click to expand...
 
S

sb1920alk

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using SUM(INDIRECT()) 2
Using 'indirect' function with full network path 0
Indirect formula errors if file is closed 0
Function Error IF Statement 3
Formula I can't figure out 2
Depedent Dropdown and INDIRECT($P$8) with a twist 4
Direct & Indirect Reports 0
Finding dynamic maxima 9

Top