0

Yes, this question has been asked before but only a workaround was ever found, which is not satisfactory for my situation. Neither is this solution satisfactory.

The question is, how can I change the default text qualifier in excel, from ", to say, a backtick (`). This would avoid the CSV file breaking, because I have many instances of unmatched double quotes in the text which stand for inches, and must be left as is. Thus with "24"" (24 inches), you can see the boundaries for the text are going to shift - which messes up the entire csv file. You can see that having `24"` solves the problem. But Excel doesn't seem to have an option to set your preferred text qualifier?

Edit: I'm well aware that the question has been asked multiple times before - so whilst it is strictly a duplicate, it is justified because all the other questions were already closed cases with accepted answers that were workarounds that are not satisfactory for me. I have certain constraints which I'll elaborate on below, but I opened essentially by clarifying that I needed a different kind of solution, ideally the most direct solution, and if not, something other than the answers already posed elsewhere.

For one thing, multiple mass search and replaces is going to increase the workload to something unacceptable. And the CSV files are provided to me already in such a way that I cannot distinguish quotes that stand for inches from quotes that serve as text boundaries in search and replace operations.

ptrcao
  • 173
  • 1
  • 2
  • 13
  • "Excel doesn't seem to have an option to set your preferred text qualifier?" Correct. Was it Henry Ford who said, "You can have any color you want as long as it's black"? In Excel, you can choose single or double quote (or none). Yes, it would be better if you could choose something like a backtick, but you can't. If your content doesn't contain any apostrophes, you're in luck. Otherwise, you need to either use the workaround of escaping the quote by doubling it, or use something else as an inches placeholder and then do a search and replace after import. Nobody said Excel is perfect. – fixer1234 Dec 01 '18 at 12:15
  • Having _"certain constraints that I don't want to go into right now"_ simply makes this no different from the duplicates. – Arjan Dec 01 '18 at 12:27
  • @Arjan Fair enough - I've elaborated. The point is the same, that the other solutions don't work for me, but now you have the elaboration on that. – ptrcao Dec 01 '18 at 12:46
  • 1
    The existing solutions not working for you is unfortunate. Sometimes there is no satisfactory solution for what you need to do. But asking the same question again doesn't magically produce a new solution. And the available solutions not being satisfactory doesn't make this a different question. – fixer1234 Dec 01 '18 at 12:53
  • @fixer1234 How about I reframe my question in terms of the specific constraints that I mentioned then? – ptrcao Dec 01 '18 at 13:09
  • 1
    I'm not sure how you could frame it differently. Your problem is clear. The bottom line is that Excel doesn't allow you to do what you need to do. The best available solution is an inconvenient workaround. The available solutions have been posted. I did a fresh search. The options within Excel and the workarounds haven't changed. I also couldn't find any third-party add-on to change Excel's capabilities. So even if you manage to frame this as a non-identical question, the answer would still be that there isn't a way to do what you want. (cont'd) – fixer1234 Dec 01 '18 at 13:27
  • 1
    But an alternative would be to post on Software Recs and ask if any other spreadsheet app or software has the capability you need. – fixer1234 Dec 01 '18 at 13:28
  • I will definitely take your suggestion and try my luck on Software Recs. I was hopeful maybe an Excel plugin? But neither you nor I have been able to find one so far... – ptrcao Dec 01 '18 at 13:34
  • _"Excel doesn't seem to have an option to set your preferred text qualifier?"_, well, just to be sure, [it has one while importing](https://stackoverflow.com/questions/42679734/importing-csv-into-excel-import-wizard-fails-to-recognize-text-qualifier-but-op/42680111#42680111), but apparently not as a some default preference. (Assuming you saw that option, as you used the term "text qualifier".) – Arjan Dec 01 '18 at 14:08
  • @Arjan Yeh, I've been using that. But what about exporting? – ptrcao Dec 01 '18 at 15:13

0 Answers0