How can i get this entire string to be imported into excel from CSV as one column (read the comma as text in the string, not a separator)?
=HYPERLINK("https://elicense.ct.gov/Lookup/ViewPublicLookupDocument.aspx?DocumentIdnt=1739916&GUID=0ECA04D1-1E18-4144-B5D2-3DF397D7CD60","Click to View")
If your users are technical enough to use the Text Import Wizard, the other answers are better for you. But if you want to produce a CSV file that can be opened without any special user interaction then read on...
It depends whether you want the user to open the CSV in Excel and see a cell with the text Click to View, which is clickable and opens the supplied hyperlink, or whether you want the user to actually see the text
=HYPERLINK("https://elicense.ct.gov/Lookup/ViewPublicLookupDocument.aspx?DocumentIdnt=1739916&GUID=0ECA04D1-1E18-4144-B5D2-3DF397D7CD60","Click to View")
The two options are shown in the following CSV file:
Quoted text,"=HYPERLINK(""https://elicense.ct.gov/Lookup/ViewPublicLookupDocument.aspx?DocumentIdnt=1739916&GUID=0ECA04D1-1E18-4144-B5D2-3DF397D7CD60"",""Click to View"")"
Quoted formula,"=""=HYPERLINK(""""https://elicense.ct.gov/Lookup/ViewPublicLookupDocument.aspx?DocumentIdnt=1739916&GUID=0ECA04D1-1E18-4144-B5D2-3DF397D7CD60"""",""""Click to View"""")"""
When opened in Excel you will see:
The Quoted text option is achieved by replacing all " characters with "", and then surrounding the whole string with " at beginning and end. This is just the standard technique for quoting in any CSV file.
The Quoted formula option is achieved by replacing all " characters with """" (4 quotes), and then prepending with "="" and suffixing with """ (3 quotes)
This Quoted formula monstrosity forces Excel to consider the contents of the CSV field to be a formula, with the value equal to the string you want it to be. To achieve that, you first construct the formula to be:
="=HYPERLINK(""https://elicense.ct.gov/Lookup/ViewPublicLookupDocument.aspx?DocumentIdnt=1739916&GUID=0ECA04D1-1E18-4144-B5D2-3DF397D7CD60"",""Click to View"")"
And then you have to turn this into a legal CSV field by doubling all quotes, and surrounding with quotes.
Values that contain commas must be wrapped in double quotes for comma separated processing. Look at the following CSV sample and how it resolves in the import Wizard in Excel.
one,two,three
Text one, "Text two, has comma", Text three
