Address Split Issue

[sanjit11]

I am trying to achieve the result shown below. If someone can assist it would be quite helpful. Thanks 🙂

[atcodedog05]

Hi @sanjit11 

Can you provide data in an excel file. And does the street address always end with street ?

[Emil_Kos]

Hi @sanjit11,

I have to build a solution for you but currently, it depends on showing the word street to work correctly.

If you will share more rows of the data in excel format I could take another look and amend it.

@atcodedog05 mentioned that it would be very helpful if you will share the data in excel format. It is time-consuming for us to rewrite all the data from the screenshot. 

The output:

[Jean-Balteryx]

Hi @atcodedog05 ,

Here is a workflow using REGEX that split the information the way you want !

As @atcodedog05 said, if it doesn't always ends with "street" you'd need to adapt it !

[sanjit11]

there u go, ive added a couple more rows!

No the address doesnt always end with street

[Jean-Balteryx · Accepted solution]

I made a second version that checks for "street" or "avenue" as example and changed your second row to replace "Street" with "Avenue". You can add more terms in the REGEX tool.

[Emil_Kos]

Hi @Jean-Balteryx,

This is super clean. Thank you for sharing. I want to understand it to improve my regex skills 😀

Could you kindly explain how this works?

(.* (?:Street|Avenue))

[atcodedog05]

_

[Jean-Balteryx]

@Emil_Kos of course !

Inside this pair of parenthesis it takes everything (.*) from the start the value until it meets either the value "Street" or "Avenue".

The | character means OR and the parenthesis with the symbols "?:" means it's an unmarked group so it's not parsed on its own but it rather allows to create a group where a value can be different from one row to another.

[Emil_Kos]

Hi @Jean-Balteryx,

Thank you for that. I believe I understand everything except one small detail.

Why you used two parentheses, I thought that parse mode keeps everything in parentheses; I don't recall seeing one inside of another.

(.* (?:Street|Avenue))