Extract Zip Code from String with Inconsistent Format

[ScottTPA]

Is there a way to extract the zip code only from an address string with inconsistent formatting? I would like to use the zip code as the geodata so I can show a map of the related customer data by zip code. Here’s an example of my shipping data. In the current format, the data is not recognized as an address format, It only:

Shipping Address
sample company, OK 74501, USA.
123 sample road Dr, Edmond, OK 73034, USA.
456 sample Avenue, Deerfield Beach, FL 33442, USA.
123 sample Highway, Hollsopple, PA 15935, USA.
124 sample Lane, Colleyville, TX 76034, USA.
123345 sample Road, Suite123, Pineville, NC 28134, USA.

[JarrettM]

Have you tried using the Column From Examples in the Query Editor ( click on Transform Data Tab on Home Tab). I would think as long as each zip code is 5 digits, it will pick it up fine. If you can’t get it to work, upload a sample PBIX file so that we can better assist.

Thanks

[ScottTPA]

Thank you. That worked for most of the items in the list. It confused some but it’s better data than I had.

[JarrettM]

When adding that column if you can get to the items it did not correctly enter the result you were looking for, you can manually enter it.

Thanks

[BrianJ]

@JarrettM, @ScottTPA,

Alternatively, you could also try running the following two Extracts:

• Brian

[BenBen2]

@ScottTPA

That worked for most of the items in the lis

This is exactly how I would have approached it. You also have to trim the data, just to be sure that there are no extra spaces. All Zip Codes will then be consistent and should work.

[ScottTPA]

Blockquote

Brian, thank you. That worked well. A few are from Canada but I can filter those out.