How to import CSV using single quotes in Excel for Microsoft 365

My company recently upgraded from Excel 2016 to Microsoft 365. Before the change, when importing from Text/CSV the wizard would have an option for the delimiter and the quote character. Now, there is an option for specifying the delemiter, but anything more requires opening PowerQuery to tweak from there (quote handling, encoding, headers, column type, etc).

So far I have found no way to specify that the CSV file (an export from another system I have no control over) uses single quotes.

• I do not want to blindly replace or remove quotes, this could have unintended consequences depending on the actual CSV content
• Including the quotes in the delimiter kind of works ('|'), but ignore the first and last of the row and, again, will break if there are quotes/commas in the content of the CSV
• I have other tools that could handle this conversion as a preprocessing step but I would much rather an Excel-only solution. If for no other reason than others at my company will need to do this also and distributing tools and training them to import a CSV seems excessive.

How can the quote character be specified using Excel for Microsoft 365?

Solution 1:

Excel 365 has both options for import. The option is called "Text qualifier":

This importer can be enabled in the Excel Options screen