Replace commas in a CSV file with a blank

[murthybhs]

Hello amazing people at Alteryx community,

I have a CSV file data that looks like this.

If an trying to use the Text to Columns tool in the Parse tool palette to split this based on the comma (,) delimiter. However, upon close observation, we can see that some companies that have Inc, LLC have a comma in between them. How do I prevent Alteryx from splitting the company name?

Any help on this is much appreciated.

[estherb47]

Hi @murthybhs 

I'd take a two-step approach to this challenge. 

First, use a text to columns with a comma delimiter, but separate into two columns only. That will get the name out.

Then you can use a RegEx tool to separate the rest. 
Use the parse method on the second column (the one that now starts with the company name), with the following expression: 

(.+),(\d+),(\d+)

The first group captures the first bit of text up until the comma that precedes the numbers. The second and third groups capture the numbers.

Let me know if that helps.

Cheers!

Esther

[MarqueeCrew]

@estherb47 ,

 how about a slight one-step modification?

(.*?),(.*),(\d+),(\d*)

 the second grouping is greedy. It takes care of inc.  

cheers,

 mark

[estherb47]

@MarqueeCrew 

Indeed, greed sometimes does pay! That works too. Love solving together.

[murthybhs]

Where should I use this? In the RegEx tool?

[estherb47]

@murthybhs 

Yes. Both options will work in the RegEx tool. @MarqueeCrew 's only uses the RegEx tool, and mine uses text to columns first, and then RegEx.

Cheers!

Esther

[MarqueeCrew]

Yes @murthybhs ,

 like @estherb47  mentioned, mine is designed for the RegEx tool in parse mode.  But if you prefer to use a formula tool you can use it with :

regex_replace([your field],"(.*?),(.*),(\d+),(\d)",'$1|$2|$3')

 you can then take it into a text to field tool as a pipe (|) delimited string. 

cheers,

 mark

[murthybhs · Accepted solution]

Thank you for the reply @EstherB47 and @MarqueeCrew. I was able to achieve this by using the Find Replace tool. I used " , INC " in the Find and "INC"
in the replace portion.