Regex_Replace Syntex Parse Error

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", "")

Workflow

Designer Cloud

Regex

Parse

Accepted answers

binu_acs

@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

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

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

atcodedog05

Hi @sslattery17

Try

IIF(REGEX_Match([Bldg-Unit], "\d+-?\d*"), [Bldg-Unit], "")

This should handle n number of numbers

Screenshot 2024-10-30 220555.png

Hope this helps : )

All comments

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

Screenshot 2024-10-30 015042.png

Hope this helps : )

Replace To Blank.yxmd

binu_acs

@sslattery17 some thing like below

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

sslattery17

@binu_acs 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.

binu_acs

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

sslattery17

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

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

binu_acs

@sslattery17 lets apply the above regex on your data and see whether you are getting the correct result. instead of the above regex you can also use the regex IIF(REGEX_Match([Bldg-Unit], "\b\w+\b"), [Bldg-Unit], "")

sslattery17

@binu_acs

I used IIF(REGEX_Match([Bldg-Unit ], "\b\w+\b"), [Bldg-Unit ], "")

It's actually removing the hyphen between the unit numbers but it does remove all the property names. I need the - between the unit numbers if they exist.

sslattery17

@atcodedog05 Thank you so much; I tried a few different Syntex but could not get the result.

I much apprecate your help and @binu_acs too on this. I am still learning so the community help is my life saver.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335