Filter and remove data

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

Regex

Help

Accepted answers

flying008

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

录制_2023_08_23_10_47_18_118.gif

flying008

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+.+$'

All comments

flying008

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

录制_2023_08_23_10_47_18_118.gif

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703