Filter and remove data

[TNShan]

Hi, I'm relatively new to Alteryx and I'm reaching out for assistance with the following dataset.

I have a dataset that requires specific filtering for the 'Address' column. I'm looking to exclude entries that start with alphabetic characters (A-Z). Additionally, for entries that begin with a numeric value followed by text, I'd like to eliminate the numeric portion, retaining only the text part. Lastly, for entries that are either pure numbers or start with '#', I'd like to remove those entirely, resulting in a 'null' column.

Once these filtering steps are completed, I aim to reassemble the dataset by merging back the previously filtered entries (data start with alphabetic characters). Hope the outcome will same as 'New_Address'. May I know what is the formula/flow that can be used in my case?

Your guidance and support would be greatly appreciated.

Address	New_Address
56
#03-204
22 DUNEARN ROAD	DUNEARN ROAD
989 BUKIT TIMAH ROAD	BUKIT TIMAH ROAD
ADAM ROAD	ADAM ROAD
COMPASSVALE DRIVE	COMPASSVALE DRIVE
WOODLANDS STREET 13	WOODLANDS STREET 13

[flying008 · Accepted solution]

Hi, @TNShan 

FYI.

IF REGEX_Match([Address], '\d+') || StartsWith([Address], '#') 
THEN Null() 
ELSEIF REGEX_Match([Address], '^\d+\D+$') 
THEN Trim(REGEX_Replace([Address], '^\d+', ''))
ELSEIF REGEX_Match([Address], '^[A-Z]+.*') 
THEN [Address] 
ELSE Null() 
ENDIF

[TNShan]

I greatly appreciate your invaluable support and guidance. May I inquire if it's feasible to incorporate a formula that transforms '15 YISHUN STREET 56' into 'YISHUN STREET 56'? The objective is to eliminate the numerical portion while retaining the street name, rather than rendering it as 'null'.

Thank you for considering this query. Your expertise is genuinely valued.

[flying008]

Hi, @TNShan 

If you want to get 'YISHUN STREET 56' from '15 YISHUN STREET 56', the above formula already cover the state , please try it .

******

If ihelp you get your want, pelase mark it as s solution and give a like for more share.

[TNShan]

I want to express my gratitude for your guidance. I've attempted the solution you provided, but unfortunately, the output resulted in 'null' for the affected entries. I'm wondering if there might be an alternative approach to resolving this issue?

I sincerely appreciate the time and effort you've dedicated to assist me. Your expertise and support are truly valued.

[flying008 · Accepted solution]

Hi, @TNShan 

Try this formula:

IF REGEX_Match([Address], '\d+') || StartsWith([Address], '#') 
THEN Null() 
ELSEIF REGEX_Match([Address], '^\d+.+$') 
THEN Trim(REGEX_Replace([Address], '^\d+', ''))
ELSEIF REGEX_Match([Address], '^[A-Z]+.*') 
THEN [Address] 
ELSE Null() 
ENDIF

As your need, I change the regex part from '^\d+\D+$' to '^\d+.+$'

[TNShan]

Wow, I'm incredibly grateful; it's working perfectly now. Your solution is truly remarkable. May I kindly pose one more question? How can I modify '305A ANCHORVALE DRIVE 24' to become 'ANCHORVALE DRIVE 24'? During my test run, the output appeared as 'A ANCHORVALE DRIVE 24'. Your assistance is immensely appreciated.

[flying008]

Dear, you need explain the logic as your get first .

[TNShan]

Thank you for your reminder. My objective is to extract the street names from the dataset.

I am interested in eliminating the '#' character from 'Address' column as part of a data cleanup process.

If entries initiate with alphabetic characters, they will remain unaltered in the output.

Subsequently, I intend to remove entries that consist solely of numbers or commence with numbers (e.g., 03-204 / 56 / 54A-4 / 14B), resulting in 'null' as the output.

Additionally, for entries commencing with a numerical value followed by text, I would like to extract the text after the first space.

I'm uncertain whether this approach is feasible in Alteryx as well. For text that concludes with a whole number, it's acceptable to retain it. However, text ending with a number containing alphabets or symbols (e.g., 112B / 11-63) should be removed.

Once these steps are completed, hope the outcome will same as 'New_Address'. 

I truly appreciate your insights and guidance on this matter. Your expertise is of immense value.

Address	New_Address
56
#03-204
22 DUNEARN ROAD	DUNEARN ROAD
989 BUKIT TIMAH ROAD	BUKIT TIMAH ROAD
ADAM ROAD	ADAM ROAD
COMPASSVALE DRIVE	COMPASSVALE DRIVE
WOODLANDS STREET 13	WOODLANDS STREET 13
#158 CANBERRA DRIVE 06-35	CANBERRA DRIVE
#ONE-NORTH GATEWAY	ONE-NORTH GATEWAY
509A YISHUN AVENUE 4	YISHUN AVENUE 4
31#07-02 ALEXANDRA ROAD	ALEXANDRA ROAD