Remove comma between double quotes

[Anonymous]

Hello Everyone,

Sorry for my english because iam french...

In my daily life, I use files from Power BI blobs. That is to say google sheet files imported via a module that transforms google sheet files into CSV.

Except, the delimiters are commas, and some cells contain commas. So this generates information lags. How to do ?
I found a solution that allows to get all columns between double quotes : 

I see a solution for which I cannot find the formula:
1. Delete the commas contained between the double quotes
2.Split columns via commas
3.Remove double quotes

But how to do this?

If you have other solutions, I'm listening!

I really need your help...please 🙂

[Anonymous]

Hi @Anonymous ,

Any updates?

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @Anonymous ,

Did you try to select all columns and replace " with nothing after split columns:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It works but I just saw that I have double quotes in the cells as well. Is there a formula that allows to remove double quotes inside 2 double quotes?

[Vijay_A_Verma]

Can you paste one such original string (not splitted) ? Don't paste the picture.

[Anonymous]

of course : 

Here is a copy of the line when I paste it into a notepad : 

"D00862,""Thu Sep 16 2021 14:42:02 GMT+0200 (Central European Summer Time)"",""quentin.miquet-sage-sc@airliquide.com"",""https://docs.google.com/spreadsheets/d/15q-8ylON6_ldJv0cvDo3ZoMQrM2v8cKh4yJu-HdAbhQ/edit"",""Terminée"",""olivier.coudert-sc@airliquide.com"",""Mon Sep 20 2021 13:50:06 GMT+0200 (Central European Summer Time)"",""Wed Sep 22 2021 11:07:59 GMT+0200 (Central European Summer Time)"",""1"",""44"",""Wed Sep 22 2021 14:46:45 GMT+0200 (Central European Summer Time)"",""Wed Sep 22 2021 14:47:40 GMT+0200 (Central European Summer Time)"",,,,""CDM - Terminée - 22/09/2021 - 'Sans commentaire' CDM - A traiter - 22/09/2021 - '302974' DMD - IncompletQ - 22/09/2021 - No comment CDM - Incomplet - 20/09/2021 - 'Il n'y a pas de plan alors qu'il est noté """"""suivant plan, spec, fichier step"""""" dans la description.'"",,""2 Création de Produit Fabriqué semi-fini"",""Mon Sep 20 2021 00:00:00 GMT+0200 (Central European Summer Time)"",""C4446E593A"",,""Boitier corps chaudronné Compresseur CC-Ø155"",""Ebauche C4446Z593A: -suivant plan et spec C4446..."

And the line directly in Power BI:

D00862,"Thu Sep 16 2021 14:42:02 GMT+0200 (Central European Summer Time)","quentin.miquet-sage-sc@airliquide.com","https://docs.google.com/spreadsheets/d/15q-8ylON6_ldJv0cvDo3ZoMQrM2v8cKh4yJu-HdAbhQ/edit","Terminée","olivier.coudert-sc@airliquide.com","Mon Sep 20 2021 13:50:06 GMT+0200 (Central European Summer Time)","Wed Sep 22 2021 11:07:59 GMT+0200 (Central European Summer Time)","1","44","Wed Sep 22 2021 14:46:45 GMT+0200 (Central European Summer Time)","Wed Sep 22 2021 14:47:40 GMT+0200 (Central European Summer Time)",,,,"CDM - Terminée - 22/09/2021 - 'Sans commentaire' CDM - A traiter - 22/09/2021 - '302974' DMD - IncompletQ - 22/09/2021 - No comment CDM - Incomplet - 20/09/2021 - 'Il n'y a pas de plan alors qu'il est noté """suivant plan, spec, fichier step""" dans la description.'",,"2 Création de Produit Fabriqué semi-fini","Mon Sep 20 2021 00:00:00 GMT+0200 (Central European Summer Time)","C4446E593A",,"Boitier corps chaudronné Compresseur CC-Ø155","Ebauche C4446Z593A: -suivant plan et spec C4446...

Thank you for helping 🙂

[Vijay_A_Verma]

If you select delimiter comma and " as Quote character, you should get right result.