Find and replace coma between double quotes

[freuzo]

Hello everyone,
I have a column wich contain strings and some of them contain double quote and values between them.
I need to find and replace comas wich can appear inside the values wich are inside those double quotes by spaces.
like :
From
A1 = 314714,cashboard,data1,"data2,mark,done"
to
A1 = 314714,cashboard,data1,"data2 mark done"

Could be formula or VBA code.

Thanks in advance.

 

[Rick Rothstein]

Here is a UDF that you can try...

Function CommaQuotes(ByVal S As String) As String
  Dim X As Long, Arr() As String
  Arr = Split(S, """")
  For X = 1 To UBound(Arr) Step 2
    Arr(X) = Replace(Arr(X), ",", " ")
  Next
  CommaQuotes = Join(Arr, """")
End Function

 

[jtakw]

Hi,

With formula, may be this.
But result will be in a separate cell, only way to result in original cell is with VBA or UDF as Rick shown.

Book3.xlsx
	A	B
1	314714,cashboard,data1,"data2,mark,done"	314714,cashboard,data1,"data2 mark done"
Sheet1013

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,FIND("""",A1)-1)&SUBSTITUTE(MID(A1,FIND("""",A1),99),","," ")

 

[jtakw]

With formula, may be this.
But result will be in a separate cell, only way to result in original cell is with VBA or UDF as Rick shown.

Oops, I meant, only way to result in original cell is with VBA only.

 

[freuzo]

All yours suggestions worked perfectly.

Thank you very much !!!?