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
Last edited by AliGW; 06-25-2021 at 08:03 AM.
With Power Query it is a two step event in the UI. Here is the resulting Mcode.
Excel 2016 (Windows) 32 bit
Please Login or Register to view this content.
Column1 Column1.1.1 Column1.1.2 Column1.2 6572 Jenn Dr Payton Township Ohio 48044-5972 USA 6572 Jenn Dr Payton Township Ohio 48044-5972 USA 5900 Timber Place Ave. Groplick Kentucky 49859 USA 5900 Timber Place Ave. Groplick Kentucky 49859 USA 5359 Midsummer ave. Porktown Kentucky 41741 USA 5359 Midsummer ave. Porktown Kentucky 41741 USA 1443 Elk trotter ave Cowcity Ohio 45756 USA 1443 Elk trotter ave Cowcity Ohio 45756 USA
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.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thank you for the response!
that looks great, but lets say i only have access to Google Sheets, can i do this in there?
Can this be done with a formula?
Try
=SUBSTITUTE(IFERROR(IF(FIND("-",A1),RIGHT(A1,14)),RIGHT(A1,10)),"USA","")
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:
Please Login or Register to view this content.
Last edited by FlameRetired; 06-24-2021 at 05:52 PM.
Dave
try this one
=LEFT(RIGHT(A1,8),4)
sory my be this one
=INT(LEFT(REPLACE(MID(A1,5,LEN(A1)),1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,5,LEN(A1))&1/17))-1,""),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.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
sorry still new hereOriginally Posted by alansidman
sorry still new here, ill be careful with being more specific moving forward.
giving me an error, doesnt like that the - is included as an integer
=SUBSTITUTE(IFERROR(IF(FIND("-",A1),RIGHT(A1,14)),RIGHT(A1,10)),"USA","")
This one works with about a 99% success rate. some of the addresses have - in them in other places (1234 Gregory-johnson ave) which is causing some errors, but thats few and far between.
Another way.
In B1 and filled down.Formula:Please Login or Register to view this content.
For Google Sheets, another alternative with RegExp;
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks