Parse address fields

[aparna0208]

Hi,

Need some help parsing address fields. I almost got it right but just few rows that are not as expected. The address fields in the excel file does not follow one pattern so when I use the regex, the result is not as expected for a few. The output includes Street No, Street Address,Suite,City,State and Zip. In some cases the suite column is correct with digits but in some cases it is appended with City and the word Suite/Ste needs to be in the Suite column but it is with the address. Also, when there is no suite number then city is populated instead of showing it as blank or null. Not sure how to handle these. The reg expression I have used is as follows: ((?:^\d+)|(?:\w+))\s([^\d]+)\s(.*)\s([^\d]+),\s(\u{2})\s(\d{5}$). I have attached snapshot which has the actual address field(in green) and the alteryx output fields after parsing it.

[aparna0208]

@fmvizcaino Just one quick question. I was reviewing the result set and noticed few are off. Please see attached snapshot. For first and third row, the street number is cut and starts from street address(8th and 49th). For second row, the word Bowling needs to be in the city column. Also, Suite/Ste needs to be in the suite column along with the number. Could you please help me out with this? 

[fmvizcaino · Accepted solution]

Hi @aparna0208 ,

It is becoming a megazord, but it is working gladly! =D

((?:^\d+)|(?:\w+))\s(.*?[(?:th)]*[^\d]+)\s((?:Suite|Ste)*\s{0,1}\d*)\s{0,1}([^\d]+),\s(\u{2})\s(\d{5}[^a-zA-Z]*)(?:.*)

Best,

Fernando Vizcaino

[aparna0208]

Ha ha I know:) this worked. thank you so much:)

Also, I am trying to parse below associate names and phones. For the phone, I just need the number without extension and +1. For names, I just need the first,middle if any, and last without those digits. (^\w+)\s(\w+) - this formula seems to work for names that have just the first and last but doesn't work for  below ones when there is middle name or any suffix like Jr or any special character. Any suggestions on this please

Associate Names

M.K. Richards (58965)

Kathy Cole (11345)

Bartley M. Lewis (63589)

John Scott Jr. (89745)

Jennifer Le Clair-Bemis (89652)

Phone

+1 (325) 8963574 x5698

+1 (720) 598-7895

[fmvizcaino · Accepted solution]

Hi @aparna0208 ,

For names, it can be like this.

(\w+)\s*(.*)\s([^\s\(]+)

For telephones.

(\+1 \(\d{3}\) (?:-?\d){7})

Best,

Fernando Vizcaino

[aparna0208]

Awesome:) really a big thank you for all your inputs on regex:)

[aparna0208]

@fmvizcaino Now I am trying to split the name column into first and last name. Below are some of the examples and it's all in different format. The output should be like the first word or character under first name column and the rest should go to last name column. Any help on this please. thank you in advance

Thai-Vi Pham
Bartley D. Gravelle

H.B. Smith

Gary Scott Jr.

Cathie Coleman

[fmvizcaino · Accepted solution]

Hi @aparna0208 ,

See if this works for you.

([a-zA-Z-]+\.{0,1})(.*)

Best,

Fernando V.

[aparna0208]

Perfect! thank you so much:) @fmvizcaino 

[Piper]

I think this would also solve my problem.  Do you know why i am getting "null" values in every result field?