Pulling out Zip Codes From Addresses on Production Formula

[Kayde_72]

I am trying to pull out the Zip codes from the prod side of Qlik Sense to use in a map. Data has been replaced for privacy reasons, but the formats are the same

Ex 1: 1234 East County Road C CA210 Mosemont, MS 55113-2513 United States

Ex 2: MouseMill CROSS, CA 15003 United States

Ex 3: 2342 West Hwy 90 East 3432 West Hwy 40 Maple, TX 76641 Martin, TX United States

The only data I want to be extracted is the parts underlined and bolded above. I have been working on another part and used 

=left(ship_from_address, 5) to get that number, but now I have a bunch of addresses that differ in length and have postal codes that are 9 long rather than 5. I only need the 5 long postal codes in each. I do not have access to dev, GeoAnalytics, or the script for this data model. I only have access to the prod side on this.

Thanks!

Qlik Sense Business  Qlik GeoAnalytics 

[Or]

If your address structure always matches the above, you could use:

Mid(Subfield(Address,',',2),5,5)

This depends on the first comma being before the state and zip. You could further refine this by using keepchar(Subfield(Address,',',2),'0123456789') first, which would keep only the numbers occurring after the first comma, and then pull the first five, which would help in cases where there's more text after the comma. However, if the structure varies too much or there might be additional commas, this won't work... it all depends on just how messy the addresses are.

[Kayde_72]

Looks like this works for the most part but there are several that have other commas

Ex: 1454 E 68th Street, Apt 345 Pizza Home Houston, TX 32689-3386 United States

The formula you have pulls the zip as 345 when it should be the 32689. Is there a way to formulate it as

"the numbers to the right of the last comma in the string, take the first 5" ?

Actually, that would not work either since the other ones have commas after the zip. Not sure what to do on this

[Or]

If you have no idea what the format mask will be, and you don't have script or original data access, I can't think of a solution. This is probably too much to ask of the Qlik front end, at least until such a day where Qlik introduces regular expression functions (no idea if that's on the roadmap or not).

I'd suggesting getting in touch with someone who does have access to the data or script and see if they can help.