0

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")

Ƭᴇcʜιᴇ007
  • 111,883
  • 19
  • 201
  • 268
jay
  • 1
  • 1
    Set the delimiter as some unused character when you import. – DavidPostill May 01 '16 at 18:08
  • thanks. except there are also commas as delimiters. and i'm trying to automate the process so the enduser who opens the CSV in excel doesn't have to alter any settings. i saw other posts that said to put the comma in quotes, or even double quotes, but nothing seems to help - the comma still breaks the string into two columns – jay May 01 '16 at 18:23
  • I don't think it's possible to set `,` as a global delimeter and override it for just one "field". – DavidPostill May 01 '16 at 18:32
  • 1
    For a global override add `sep={some_character)` as the first line of the csv file, for example `sep=|`. You still have to uncheck Use system separators in Options>Advanced though. – DavidPostill May 01 '16 at 18:34
  • See [Easiest way to open CSV with commas in Excel](http://superuser.com/a/730509) – DavidPostill May 01 '16 at 18:35

2 Answers2

1

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:

CSV viewed in Excel

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.

John Rees
  • 156
  • 3
0

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

enter image description here

teylyn
  • 22,498
  • 2
  • 40
  • 54