Google Sheets: Extracting Zip codes with in multiple forms.

Hello! I need help finding a way to extract zip codes... the problem is is that there are some with 4 numbers after the dash and some without. but i need to extract either way. i have included some samples below, edited for privacy.... The entire block of text below is in its own cell (per row)

6572 Jenn Dr Payton Township Ohio 48044-5972 USA
5900 Timber Place Ave. Groplick Kentucky 49859 USA
5359 Midsummer ave. Porktown Kentucky 41741 USA
1443 Elk trotter ave Cowcity Ohio 45756 USA

With Power Query it is a two step event in the UI. Here is the resulting Mcode.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1.1", "Column1.1.2"})
in
    #"Split Column by Delimiter1"

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	Column1		Column1.1.1	Column1.1.2	Column1.2
2	6572 Jenn Dr Payton Township Ohio 48044-5972 USA		6572 Jenn Dr Payton Township Ohio	48044-5972	USA
3	5900 Timber Place Ave. Groplick Kentucky 49859 USA		5900 Timber Place Ave. Groplick Kentucky	49859	USA
4	5359 Midsummer ave. Porktown Kentucky 41741 USA		5359 Midsummer ave. Porktown Kentucky	41741	USA
5	1443 Elk trotter ave Cowcity Ohio 45756 USA		1443 Elk trotter ave Cowcity Ohio	45756	USA
6

Sheet: Sheet1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Edited: I may have misread the intent. This will extract the 5 character portion only.

I've not worked with Google sheets but I have difficulty believing this won't work. With the posted data in column A this in B1 and filled down.

Formula:

=MID(A1,IFERROR(FIND("-",A1)-5,LOOKUP(1E+306,FIND(" ",A1,ROW($A$1:$A$100)))-5),5)

Power Query is a Microsoft product that is part of Excel. It is not compatible with Google Sheets. Suggest in the future that you identify that you are working in Google Sheets so that those of us who don't will not be wasting our/your time with solutions that will not work for you.

Moved to a more appropriate forum section, as the whole set-up, as explained above, was misleading from the start.

Another way.

In B1 and filled down.

Formula:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",256)),256*2),256))

	A	B
1	6572 Jenn Dr Payton Township Ohio 48044-5972 USA	48044-5972
2	5900 Timber Place Ave. Groplick Kentucky 49859 USA	49859
3	5359 Midsummer ave. Porktown Kentucky 41741 USA	41741
4	1443 Elk trotter ave Cowcity Ohio 45756 USA	45756