Best tool

[hi2019]

Hi,

What is the best toll for this?

if zip code is not 5 or 9 digits  not counting hyphen (-)- delete # completely

if zip code has a hypthen (-) delete HYPTHEN

desired output:

45678-96  output delete

45768-1589   output 45768 1589

45768-           output 45768

[alexnajm]

probably a Formula with Regex is my suggestion!

[alexnajm]

IF RegexMatch([Zip Code], "\d{5}-\d{4}") OR RegexMatch([Zip Code], "\d{5}-") THEN Left([Zip Code], 5) ELSE null() ENDIF

Edit: IF REGEX_Match([Zip Code], "\d{5}-\d{4}") OR Regex_Match([Zip Code], "\d{5}-") THEN Trim(Replace([Zip Code], "-", " ")) ELSE null() ENDIF

[OTrieger]

@hi2019 

The best solution will be a one that you are familiar with. So if you are familiar with Regex then I would follow @alexnajm solution. If not familiar with Regex and is urgent then simple Replace formula will do too. If you have time to get more familiar with Regex spend time on it as Regex is a very powerful tool and can give you solutions for many different scenarios.

[CoG]

@alexnajm 's formula does not quite generate the stated output. Here is a modified version of his formula that does better:

IF Regex_Match([Zip Code], "\d{5}-?(?:\d{4})?") THEN TRIM(REGEX_Replace([Zip Code],"(\d{5})-?(\d{4})?","$1 $2")) ELSE null() ENDIF

Hope this helps and Happy Solving!

[alexnajm]

Edited with testing @CoG