Parse Address with commas in middle of address.

[lbolin]

I am trying to parse address with a delimiter of a comma, I am using the text to column tool but i am running into a problem when there is a comma in the middle of the address. Example:

1300 Meridian Street,Suite 204,Huntsville,AL,35801,US

I want it to be :

130 Maridian Street Suite 204

Huntsville

AL

35801

US

It also shows up as:

1 Chase Corporate Dr. Suite 400,Ste. 6-200,Birmingham,Alabama,35244-1026,US

Also

[clmc9601]

Hi @lbolin,

How about this?

 

[AkimasaKajitani]

Hi @lbolin 

You can use RegEx tool.

Regular Expression:

(.+),(.+),(.+),(.+),(.+)

[danilang]

Very good solution @AkimasaKajitani 

Can you also explain why this works?  It will help others who aren't as familiar with regex?

Thanks

Dan

[AkimasaKajitani]

Hi @danilang 

For example, (.+),(.+) shows this result.

(.+),(.+)

+ is longest match. So the first (.+) shows this.

  1300 Meridian Street,Suite 204,Huntsville,AL,35801

Next shows this. 

  US

For the longest match pattern, search for the longest match possible.

On the other hand, Shortest match pattern is this(Add '?').

(.+?),(.+)

In this case, (.+?) pattern shows to first comma.

[BretCarr · Accepted solution]

@lbolin 

I believe the REGEX expression should be a bit more specific to increase speed as well as handle that pesky extra comma:

(.+),(.+),(.+),((?:[\d-]{10}|\d{5})),(.{2})

Using parse method in the regex tool should take care of your issue!

Cheers!