Request for help: Workflow to parse text and calculate drive time

Question

Good morning, I hope this hasn't been answered already and I missed it. Please see the attached Excel file. I am trying to accomplish two things in a single workflow, if possible:

* Looking at Column E (Offices): I need to extract any text that cites an office in either New York, New Jersey, or Connecticut and pull that text into a new column. Example: see row 10 - you have to double click on that cell under Offices to see the full view of the company's offices. I need Alteryx to find the office at 80 Pine St New York, New York, and put that information into a new column.
* Once that's done, I'd like to then be able to calculate the distance from my firm's NYC office to that location. My office is at 30 Rockefeller Plaza
New York, New York 10112-4498.

I understand that #2 might not be doable if I only have the base Designer platform, but even accomplishing #1 would be a huge help. Thanks in advance!

Drive Time from NYC Office.xlsx

Yoshiro_Fujimori · Accepted Answer

Hi @nicolecsintyan ,

For part 1, here is one of the potential solutions.

Workflow

Output

The companies which have multiple address in NY/NJ/CT have multiple lines.

Part 2 may be difficult without the coordinate data of the location.

If it can be approximate distance, you may use the coordinates of ZIP code available on the web, such as:

https://gist.github.com/erichurst/7882666