Filter cells with house numbers

[JamesDB]

Hi folks,

My query relates to house numbers and how to filter them out when they also have a letter or special character. 

In the screen grab attached I want to filter out records 2 to 4 so that I can then concatenate the remaining records so that they form 1 address line e.g. 1-2 Wesley Gate or 10a The Market or 11-12 Critchett Terrace, so that they look similar to 10 Union Court.

There are some records, such as screen grab 2 where the order is different e.g. building 4.

I've have tried numerous things but the closest I've got to cracking it is: 

if REGEX_Match([Building Name], ".*\d.*\w")
then [Building Name] + " " + [Dependent Thoroughfare & Descriptor]

else Null()
endif

but it is concatenting records that I don't want to such as those in screen grab 3.

Any help would be greatly appreciated.

[KarolinaRoza]

Hi,

You are considering records from screen 3 as you have ".*" at the beginning of your formula:

if REGEX_Match([Building Name], ".*\d.*\w")

but if you eliminate it then it will not work for records like in screen 2: 'Building 4'.

Records from screen 2 and screen 3 are very similar for me, is there anything what differentiate these sets to be able to idenfify which should match which not? 

Regards,

Karolina

[Maskell_Rascal]

Hi @JamesDB 

If I'm understanding the request correctly, this formula should give you the desired output. 

IF IsEmpty(REGEX_Replace([Building Name], '[^\d{1,}\-].', '')) 
THEN [Building Name]+' '+[Dependent Thoroughfare & Descriptor] 
ELSE REGEX_Replace([Building Name], '[^\d{1,}\-].', '')+' '+[Dependent Thoroughfare & Descriptor] 
ENDIF

I've also attached a sample workflow for you to try. 

If this solves your issue please mark answer as correct, if not let me know!

Thanks!

Phil

[JamesDB]

Hi Phil,

Thanks for your response.  It works for most of the records apart from record 9, (building 4), record 10 (building 6) and record 10 as the two fields don't need to be merged for these records.

[Maskell_Rascal]

@JamesDB those two records are being joined from the IF/THEN command in the formula logic. It was unclear from your initial post what your desired output was for those fields, so I assumed they needed to be combined. If you can provide some sample data with the desired output, I can take a look at revising the workflow to fit your needs. 

Thanks!

Phil

[Ben_H]

Hi @JamesDB,

Is this the sort of output you were looking for?

I used a slightly different approach, which looking at it seems a bit silly now but it seems to work on the face of it -

replace([Building Name],REGEX_Replace([Building Name], '([\d+][\S*]{1,}|\d*)',''),"") +" " +[Dependent Thoroughfare & Descriptor]

It actually replaces the marked group with nothing, and then uses an ordinary replace to subtract this from the original field. I couldn't immediately think of a better way to do this within the formula tool in one go.

It may fall over on more complex addresses of course, Regex isn't my strong suit!

Regards,

Ben

[JamesDB]

Hi Phil,

Sorry it's taken a while to get back you it's been hectic of late!

Basically what I'm trying to achieve is separate out house numbers which have a letter after them eg 1a, 3c from words that have a number with them e.g. Unit 1 or building 6.  I then want to concatenate them with contents of the Dependent Thoroughfare & Descriptor field but if that is null then concatenate with the Thoroughfare & Descriptor field.

I've attached a raw sample file and a desired outcome file for you to play with.

[JamesDB]

Hi Ben,

Thanks for your response, please see reply I've posted to Phil

[bensilv · Accepted solution]

Hi @JamesDB 

I took a more simple approach that should get you most of the way there!

Using the RegEx parse tool, I identified all patterns that would match what you are describing (e.g. 14c, 14., 14-15). Once we had that parsed out, apply some logic to test other fields (e.g. if there is a Sub Building Name, take that as the first line of address).

Finally, just to check that it is as expected with your data, added a formula tool to compare the expected vs parsed.

Ben

[JamesDB]

Hi @bensilv 

Thanks for your help on this.  The RegEx has worked a treat on this particular segment of records.