REGEX - Delete specific characters

[Skyrone]

Hello everyone,

After spending a few hours on the forum and Alteryx, I decided to ask for your help.

Here's a sample of the data that I have and that I'm trying to standardize.

Field_1

10;"abc";"def";A

20;"ab " c"";"def";B

30;"Abc"";"";C

40;"abc";"de ; f";D

Expected output is the following :

Field_1	Field_2	Field_3	Field_4
10	abc	def	A
20	abc	def	B
30	abc	def	C
40	abc	def	D

But as you can see :

- On line 2 the 'c' is between double quotes

- On line 3 there's an extra double quotes in what must be the Field_2

- On line 4 there's a semicolon in what must be the Field_3

Because of line 4, I cannot ignore delimiters in double quotes.

So I started by a simple formula to ignore delimiters when possible :

Then I tried this REGEX but it deletes every quotes and I don't know how to say "delete only the double quotes that are in the middle of the Field :

Do you have any idea... ? 

It's the first time I use REGEX and I cannot find the right formula.

Thank you in advance,

Regards

[binuacs · Accepted solution]

@Skyrone One way of doing this with the replace formula

ReplaceChar(Replace([Field_1],' ; ',''), '" ', '')

[Skyrone]

Hi @binuacs,

Thank you, it seems to work in every cases ! I didn't know this formula ReplaceChar.

Cheers,