Hi, I’m looking for help with cleaning up my address field. I receive the data in all caps. I typically will change the field to title case, but this also changes NW to Nw, NE to Ne… how can I keep the street directions in all caps? Address Address 2CityStateZipThis is what I get125 BLUE STREET NWAPT. 2WASHINGTONDC20007 258 GREEN STREET NESUITE 3WASHINGTONDC20007 896 YELLOW STREET SW#9WASHINGTONDC20007 7896 RED STREET SE WASHINGTONDC20007 Address Address 2CityStateZip 125 Blue Street NwApt. 2WashingtonDC20007This happens with title case258 Green Street NeSuite 3WashingtonDC20007 896 Yellow Street Sw#9WashingtonDC20007 7896 Red Street Se WashingtonDC20007 Address Address 2CityStateZipThis is what I want125 Blue Street NWApt. 2WashingtonDC20007 258 Green Street NESuite 3WashingtonDC20007 896 Yellow Street SW#9WashingtonDC20007 7896 Red Street SE WashingtonDC20007

Address Clean up with NE, SE, NW, SW

Hi, I’m looking for help with cleaning up my address field. I receive the data in all caps. I typically will change the field to title case, but this also changes NW to Nw, NE to Ne… how can I keep the street directions in all caps?

	Address	Address 2	City	State	Zip
This is what I get	125 BLUE STREET NW	APT. 2	WASHINGTON	DC	20007
	258 GREEN STREET NE	SUITE 3	WASHINGTON	DC	20007
	896 YELLOW STREET SW	#9	WASHINGTON	DC	20007
	7896 RED STREET SE		WASHINGTON	DC	20007


	Address	Address 2	City	State	Zip
	125 Blue Street Nw	Apt. 2	Washington	DC	20007
This happens with title case	258 Green Street Ne	Suite 3	Washington	DC	20007
	896 Yellow Street Sw	#9	Washington	DC	20007
	7896 Red Street Se		Washington	DC	20007


	Address	Address 2	City	State	Zip
This is what I want	125 Blue Street NW	Apt. 2	Washington	DC	20007
	258 Green Street NE	Suite 3	Washington	DC	20007
	896 Yellow Street SW	#9	Washington	DC	20007
	7896 Red Street SE		Washington	DC	20007

Designer Cloud

Accepted answers

All comments

Raj

Do the Title case and then find and replace the directions.

Raj

Another way is to parse the right 2 character which will separate the direction and then do title case and then join back.

hope this solve your problem.

TinaLong

When I do a find and replace, it also replaces, for example, 123 SEed Street, 258 NEwton Street...

TinaLong

@Raj Okay, but what if my address has more information after NW, for example, 123 Blue Street NW Apt 3.

I probably should have included that earlier.

Raj

replace
eg- " Ne " with " NE "

keep space on both side if there is extra data.
this should help

Raj

Please find attached for reference

comm 473.yxmd

binu_acs

@TinaLong Another option

Address_Cleansing_191223.yxmd

TinaLong

@binu_acs This one works except for one piece. When I have extra data after the SW or NW, etc. in the same field, for example, 7896 RED STREET SE Apt 9.

My fault, I added that piece of information after my original question. Right now, your multi field formula works if the field ends in NE, NW, SE, or SW. Can you help me with the formula if there is additional data afterwards?

CoG

Here is another work flow that handles everything with not too complicated Regex Formula:

Sample Workflow.png

Address_Cleansing_191223.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335