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.