0

I'm trying to create a CSV by inserting an array of strings. The problem I have is I'm trying to use the =HYPERLINK function to create links to google maps. The problem is that Google Maps uses a comma when using coordinates like this https://www.google.com/maps/search/?api=1&query=30.1582, -95.7767 so when I use that and open in Excel it splits on the comma putting the -95.7767 into another cell breaking the =HYPERLINK function.

I've tried escaping and surrounding the comma with quotes, but I just can't get it to stop splitting on that comma. Here's my current. With this I actually see a comma in Excel but only one and it still splits on the other commas.

"=HYPERLINK(\"https://www.google.com/maps/search/?api=1&query=${boothDbObj.weightedAveLoc.coordinates[1]}\"\",\",\"\"\"${boothDbObj.weightedAveLoc.coordinates[0]}\", \"${boothDbObj.weightedAveLoc.coordinates[1]} ${boothDbObj.weightedAveLoc.coordinates[0]}\")"

with this the Excel cells look like =HYPERLINK("https://www.google.com/maps/search/?api=1&query=39.284904078422855"" then beside that cell is ,"-76.56469143542125" and beside that cell is "39.284904078422855 -76.56469143542125")

Matt
  • 59
  • 1
  • 11
  • this might help: https://stackoverflow.com/questions/4617935/is-there-a-way-to-include-commas-in-csv-columns-without-breaking-the-formatting – Majid Roustaei May 13 '20 at 13:18
  • 1
    There is RFC 4180. And you are usually way better off using a conforming library instead of banging strings together. – cfrick May 13 '20 at 13:22
  • Just use [Apache Commons CSV](http://commons.apache.org/proper/commons-csv/). – David Conrad May 13 '20 at 14:26

1 Answers1

0

Try to define another delimiter as a comma. Excels works normally also with semicolon. Also you can quote a colum with quotation marks:

Example 1:

  Row 1;https://www.google.com/maps/search/?api=1&query=30.1582, -95.7767
  Row 2;https://www.google.com/maps/search/?api=1&query=30.1582, -95.7767

Example 2:

  "Row 1, with comma";"https://www.google.com/maps/search/?api=1&query=30.1582, -95.7767"
  "Row 2, wich comma";"https://www.google.com/maps/search/?api=1&query=30.1582, -95.7767"
omido
  • 76
  • 5