Regex_Replace Syntex Parse Error

[sslattery17]

I have a column Bldg-Units that has a lot of data, both unit and property name; I am trying to replace the property names with blank or empty cells leaving remaining data that I will fill down at a later time.  I am getting parse error, can someone help me figure out what I am doing wrong?  Thanks in advance. 

regex_replace([Bldg-Unit ], "11th & Spruce",
"250 High",
"600 Goodale",
"80 On The Commons",
"801 Polaris",
"Baxter Park",
"Bracken House",
"Buckstone Flats","Burrough"s Mill",
"Devonshire",
"Dixon House",
"Edison at Gordon Square",
"Emerson Park",
"Gateway Lakes",
"Gateway Lofts Centerville",
"Gateway Lofts Columbus",
"Gateway Lofts Lansing",
"Gateway Lofts Lexington",
"Graham Park at The Highlands",
"Harper House at The Highlands",
"Heritage Green",
"Lane Lofts",
"Lofts at Norton Crossing",
"Lumina",
"Luxe 88",
"Luxe at The Highlands",
"Madison House",
"Marina Lofts",
"Marine Club",
"Market & Main",
"Minnetonka Station",
"Mulberry Lofts",
"North Shore Flats",
"Packard Building",
"Park"s Edge at Shelby Farms",
"Pennbrook Station",
"Polo Run Apartment Homes",
"Pulliam Square",
"Rize at Opus Park",
"Spectra",
"Spring House at Brandywine",
"Station 324",
"Steel House",
"Stillwell at Avery Centre",
"Stillwell Jerome",
"Stilwell at Wellen Park",
"Sugar Run",
"The Aubrey",
"The Baylor",
"The Darby at Briarcliff",
"The Flats at Douglas",
"The Gramercy",
"The Jacqueline",
"The Kingston",
"The Monarch",
"The Normandy",
"Preserve at Forest Creek",
"The Reserve of Sugarcreek",
"The Stratton",
"The Thomas",
"View on Grant",
"Tribeca",
"Willis Avondale Estates", "")

[atcodedog05]

Hi @sslattery17 

This formula will not work. The syntax will not work that way.

You can try find and replace tool

https://help.alteryx.com/current/en/designer/tools/join/find-replace-tool.html

It should fit perfectly for this situation.

[atcodedog05]

Hi @sslattery17 

It would something like below

Hope this helps : )

[sslattery17]

@atcodedog05 I am not getting results expected since the column has unit number that contain letters also.  Some have for example 200A or 200-g or B200 it is stripping all letters...

[atcodedog05]

Hi @sslattery17 

Can you please provide more accurate sample data and replace list and place where you facing the issue.

Others can help you with that.

[sslattery17]

@atcodedog05 

I have attached sample data.  I am trying to remove the property names out of the column [Bldg-Unit] so the column has only unit numbers for each property.

[binuacs]

@sslattery17 some thing like below

IIF(REGEX_Match([Bldg-Unit], "^\d{4}-\d{3}$|^\d{3}$"), [Bldg-Unit], "")

[sslattery17]

@binuacs That is getting to result but leaving out the follwoing unit numbers.  I know because the data sample did not include all unit variations; 466-0401, 13-203, 6-204, K840-103, 38-5371 D, 3-304EL, 201E...this is why I thought it would be easier to remove the property names from that column.  

[binuacs · Accepted solution]

@sslattery17 If you have a pattern which you wanted to keep then regex will help to get the desired result, what i would suggest is to analyse your data and try to find a pattern then write the regex based on that, in the below regex, i have used the pattern first 4 digit then - then 3 digit(5516-101) or starts with 3 digit (201) 

IIF(REGEX_Match([Bldg-Unit], "^\d{4}-\d{3}$|^\d{3}$"), [Bldg-Unit], "")

[sslattery17 · Accepted solution]

@binuacs what do you think of this; do you think this would get me there?

IIF(REGEX_Match([Bldg-Unit ], "\<\w+\>"), [Bldg-Unit ], "")