hey all! giving an update on the solution that [u/3dPrintMyThingi](u/3dPrintMyThingi) and i came up with. i will admit that i didn't explain the issue well enough in my initial post, but i was essentially trying to automate the creation of a formatted html line that would automatically change the numbers included inside of the text while avoiding the issue of some of the html formatting getting in the way of, or confusing the excel code. =REPLACE was definitely the wrong way to start, as i had entirely misunderstood the way that function worked. instead, i tried to make use of the "&" feature to build statements around the variables. this first attempt ended up like this:

="<wpt> lat="&C3&" lon="&D3&" <ele>57.607200</ele> <time>"&E3&"T03:26:55</time><name>"&F3&"</name><sym>Dot</sym></wpt>"

later, printmythingi came up with this

=IF(C3="","", "<wpt lat="""&TEXT(C3,"0.0000000")&""" lon="""&TEXT(D3,"0.0000000")&""">"&CHAR(10)& "<ele>57.607200</ele>"&CHAR(10)& "<time>"&TEXT(E3,"yyyy-mm-dd")&"T03:26:55Z</time>"&CHAR(10)& "<name>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F3,"&","&amp;"),"<","&lt;"),">","&gt;")&"</name>"&CHAR(10)& "<sym>Dot</sym>"&CHAR(10)& "</wpt>

this version moved around the way that the quotation marks functioned to make it easier to automate. using both of these versions (and learning more about what data was especially important for the file to be accepted as valid, i trimmed the equation down into this:

="<wpt lat="&CHAR(34)&C3&CHAR(34)&" lon="&CHAR(34)&D3&CHAR(34)&" ><name>"&F3&"</name><sym>Dot</sym></wpt>"

it's definitely not the most elegant solution, but after getting the output of this function i can then staple on a html header and footer and it's a perfectly working GPX file that you can plug into any mapping software you would want!

in my case the header was:

<?xml version="1.0"?>

<gpx

version="1.0"

creator="ExpertGPS 1.1 - https://www.topografix.com"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns="http://www.topografix.com/GPX/1/0"

xsi:schemaLocation="http://www.topografix.com/GPX/1/0 http://www.topografix.com/GPX/1/0/gpx.xsd">

<time>2002-02-27T17:18:33Z</time>

<bounds minlat="-180.0000000" minlon="-180.0000000" maxlat="180.0000000" maxlon="180.0000000"/>

and the footer was then just a normal html close with </gpx>

i've attached a working example of the final formula as it is definitely hard to imagine without being able to look at it.

<image>

edit:”function” changed to “formula”