Address Clean up with NE, SE, NW, SW

[TinaLong]

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

[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

[binuacs]

@TinaLong Another option

[TinaLong]

@binuacs 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: